Replies

Hi Mike,
After your recordset is opened try a debug.print or msgbox to show the values of rs.cursortype and rs.locktype. Compare the values retrieved with the constants adOpenDynamic and adLockBatchOptimistic as shown in the Object Browser. Check out MSDN library info on ADO and the Jet OLEDB provider. If you ask for a type of recordset not supported by Jet it will substitute something else; this may be causing your problem. Also, did you mean to leave the .Update statement commented out? I think this will take you to a good starting point in your search: http://msdn.microsoft.com/library/default….date_topic5.asp

Wendell,
Thanks so much for the suggestions! I will check out the Knowledge Base articles to see if we can improve on the locking strategy. I have seen their article regarding the bug in Access 2K that prevents row-level locking from working when opening the database from a shortcut. Do you know of any way to avoid using a shortcut when one has to include a workgroup file for security in the startup? Meanwhile, I will read the white papers and try to implement some of their suggestions for avoiding locking problems. Thanks again for your willingness to help; I have learned a lot from your posts on other questions. We’ve been fighting unexplained growth of our front-end until I happened upon your advice to someone else to make it readonly. Works like a charm!

I don’t really know what’s wrong here, but will try a couple of stabs in the dark. Try doing a rs.Movelast before doing the rs.recordcount. I don’t know why your code is acting differently in the different versions, and inconsistently at that, but it might be worth a shot. Also, sometimes with all the subforms, Access gets confused about who “Me” is; you might want to try expanding out the “Me” to Forms!frmsoandso…etc.

Mike,
Unfortunately, the listbox columns in VB are not like the listbox columns in Access. If you stretch the height of your listbox to accomodate more rows you will see somewhat different results, but not what you want. As you’ve discovered, the listbox with columns scrolls horizontally, not vertically. If the data in your tables is all the same width you might try setting columns back to 0, and putting tabs between the fields:
listbox.additem rs(1) & vbTab & rs(2)

Hmmm, it seems like your Visual Basic Editor behind Access is behaving oddly. Anyway, there are still a few things you can try. First priority is fixing your references since one did come up marked “MISSING”. There is a Knowledge Base Article that explains how this can break all the code and cause the error message you have gotten: http://support.microsoft.com/support/kb/ar…s/Q160/8/70.asp You can try to resolve it by unchecking the MS Windows Common Controls Reference in the reference window, unless you are using those controls in your project. Usually, that particular reference comes from adding additional tools to the toolbox, I think. As for the missing views in your VB environment, here are some things to try: If you are seeing only the Immediate Window when you go to the VB environment after clicking the ellipsis in AfterUpdate you can try either the F7 key or View –> Code to see if you can get a code window showing your objects and events. If you are lucky enough to have that appear, choose the combobox name (combobox1?) from the leftmost dropdown, then AfterUpdate from the rightmost dropdown. Any code appear? To try to get the Object Browser to appear, can you choose View –> Object Browser from the menu, or does nothing arrive on screen? Good Luck!

Charles,
If you click on the ellipsis …. next to the [Event Procedure] of the AfterUpdate property, it should open a code window to the Visual Basic editor to see the code that the Wizard wrote for you. That is where you can check Tools –> References to make sure that all the appropriate references are set.

Just wanted to add that I am not at all disagreeing with Charlotte’s suggestion of a good VB Database book to learn about all the issues involved when manipulating databases through VB. There are lots of details that Access handles so well that need to be addressed with code in VB. But sometimes it is kind of fun to do things the hard way.

Jerry,
If you still want to do this in VB here are a few things that might give you some ideas. This example populates the combo box with CompanyName from the Customers table of Northwind. When a user clicks on a name, a couple of textboxes will be populated with data for that customer:
In general declarations of the form: Dim cnNorthWind as ADODB.Connection
Then load the combobox when the form loads, limiting the recordset to one field
Private Sub Form_Load()
‘ Establish a connection to the Access database
Set cnNorthwind = New ADODB.Connection
With cnNorthwind
.Provider = “Microsoft.Jet.OLEDB.4.0”
.ConnectionString = “Data Source=C:Program FilesMicrosoft Visual StudioVB98Nwind.mdb;Persist Security Info=False”
.Open
End With
‘
Dim rsCustomers As ADODB.Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = cnNorthwind
rsCustomers.Open “Select CompanyName from Customers”
Do Until rsCustomers.EOF
cboNames.AddItem rsCustomers!CompanyName
rsCustomers.MoveNext
Loop
‘ might as well free up the memory
rsCustomers.Close
Set rsCustomers = Nothing
End Sub

In the ComboBox’s Click event, go get the data matching the selected item; this example keeps it simple by querying the same table, but of course you could build a more complex SQL statement with joins and stuff – use Access QBE to do it and paste the SQL view from Access into your code.
Private Sub cboNames_Click()
‘Purpose: Retrieve values for the selected customer
Dim strSQL As String
Set rsCustInfo = New ADODB.Recordset
‘ Use the connection already opened in Form’s Load event
rsCustInfo.ActiveConnection = cnNorthwind

‘ Choose the appropriate cursor and locks for your project
rsCustInfo.CursorType = adOpenStatic
rsCustInfo.LockType = adLockOptimistic

‘Note: The concatenation here is not the most robust! Needs improvement
‘ The SQL statement will have syntax errors if there are embedded quotes in cboNames.text

strSQL = “Select City, ContactName FROM Customers WHERE CompanyName = ‘” & cboNames.Text & “‘”
rsCustInfo.Open strSQL

‘ If you want to bind the fields to controls on the form
Set txtCity.DataSource = rsCustInfo
txtCity.DataField = “City”
Set txtContact.DataSource = rsCustInfo
txtContact.DataField = “ContactName”
End Sub
Note: If you only want to show the data and not allow changes use this instead of the datasource and datafield properties.
txtCity.Text = rsCustInfo!City
txtContact.Text = rsCustInfo!ContactName

There’s lots more to consider; you’ll need to add code to do updates of the recordset if needed, and decide the best way to handle connections and queries. If there aren’t tons of records you might want to retrieve all the data in the Load event such as:
rsCustomers.Open “Select CompanyName, ContactName, City from Customers”
You’d still populate the combobox from the CompanyName field; then in the combobox click event you can just apply a filter instead of running another query against the database:
rsCustomers.Filter = “CompanyName = ‘” & cboNames.Text & “‘”

Hi again Troy,

One more thing that might help. Use the Controls.Add method to create and position the controls but don’t try to add the items to the combobox right then and there. You can handle populating both comboboxes with the same data in the UserForm’s AddControl event which will fire after each control is added. If the type of control just added is a combobox just fill it with the values needed.

Private Sub UserForm_AddControl(ByVal Control As MSForms.Control)
If TypeOf Control Is ComboBox Then
Control.AddItem “Lounge”
Control.AddItem “Lex”
Control.AddItem “Lizard”

End If

End Sub

Hi Troy,

I think that the Controls collection of the UserForm is what you need. Here is an example that uses a command button to add a new label, named lblPhoneNumber, making it visible, then setting the appropriate properties:

Dim DesiredControl As Control
Private Sub cmdAddControls_Click()

Set DesiredControl = Controls.Add(“Forms.Label.1”, lblPhoneNumber, Visible)
DesiredControl.Left = 18
DesiredControl.Top = 100
DesiredControl.Width = 175
DesiredControl.Height = 20
DesiredControl.Caption = “Phone No.”

End Sub

To add a combobox the first parameter to the add method would be “Forms.Combobox.1”. You can find more details in the Help topic of “Controls Collection”. Have fun!

Wendy,
Might it have something to do with Internet connections during the print operation? I am just guessing ’cause it seems like when you copy and paste from a Web page into Word it inserts a Hyperlink to the original location of the graphic on the Web. I have no idea why this might interfere with printing; in fact, on my computer such graphics print fine. Maybe if you remove the Hyperlink after pasting a picture, the print would behave better?

You’re welcome! I am glad that Jayden’s solution was exactly what you needed. I have just recently discovered The Lounge and also find it to be an amazing resource! I am learning so much from reading people’s solutions. Good luck with your project!

Are the WingDings and Symbol fonts in complete words? You might be able to use the Words collection of the document, something like:

For Each aword In ActiveDocument.Words
If aword.Font.Name = “Wingdings” Then
MsgBox “Leave the Dingbat alone!”
Else
aword.Font.Size = 18
End If

Next aword

If the special characters are just individual scattered characters you can use the Characters collection instead… For each aChar in ActiveDocument.Characters …and so forth

Could you put the code in the GotFocus event of the Name field instead of in the Phone Number and Fax Number fields? PhoneNumber.visible=true. Then in the LostFocus event code for Name: PhoneNumber.visible=false

Hi Charles,
It sounds like there might be a problem with missing references. Would you please check a couple of things?:
While viewing the code in the coding window, hit the F2 key to bring up the Object Browser. Its topmost combo box contains a list of Projects and Libraries that your application is referencing. It probably will contain: Access, ADODB, stdole, VBA, and the name of your application. Next choose Tools –> References from the menu. You should see a corresponding checkmarked entry for each Library listed in the Object Browser except for your application’s name. If any of them are missing, your references need fixing. As you highlight each selection note the path and name of the file (.olb, .tlb, or .dll) at the bottom of the reference dialog box. For the combobox wizard code the most likely missing library is either Access or VBA. Microsoft Access 9.0 Object Library should point to C:Program FilesMicrosoft OfficeOfficeMSACC9.olb. Your path may be different; you may have to browse for it. Visual Basic for Applications on my machine points to C:Program FilesCommon FilesMicrosoft SharedVBAVBA6 Sorry, I can’t see the whole name on my screen so not sure which file it is.

Good luck!