Combo Box to populate Text Fields via Recordset

[cstickman]

Hey Everyone,

I just started to mess around with recordsets and I am having a bit of trouble. I have a combo box linked to a query and that works great. I have a textbox labeled txtid and the rowsource for that field is =[cmbvendor].[columns](1). The combo box is labeled cmbvendor. I then have a bunch of other text boxes. So my thought was in the after update field of the combo box have it populate the rest of the text fields based off of the txtid box. I would have done [columns] for all of it, but we need to be able to update the text fields with new information. So below is the code and I was trying to use an SQL SELECT statement with the WHERE clause. If anyone could offer some assistance that would be greatly appreciated as I am sure I am missing an easy step. Thanks!!

 Option Compare Database
Option Explicit
  
 Dim db As Database
Dim rs As Recordset
  
 Private Sub cmbvendor_AfterUpdate()
Dim strSQL As String
    
    Set db = CurrentDb
    Set rs = strSQL
     strSQL = "Select * from tblcontacts Where ID = '" & Me.txtid & "'"
    
    Me.cmbvendorstatus = rs!vendorstatus
     etc....
  
 End Sub

 

[sneuberg]

If you need to update the information in the textboxes then this is not the way to go. The fields you want to update should be in the Record Source of some form. If these fields are not in the table that this form is bound to then you should create another form for those field bound to their table. If the tables are related then you would normally put it in a subform on this form. Please note that where I write table you could substitute query based on table.

This is probably confusing. If you could give me the names of all of the tables and fields involved I could be clearer.

 

[sneuberg]

For future reference I'll comment on your code

Set rs = strSQL

You would never set a recordset object to a string. Normally this line would look like

Set rs = db.OpenRecordSet(strSQL)

This line assigning the SQL state to the string variable strSQL would have to be placed before you use it to open the recordset.

 strSQL = "Select * from tblcontacts Where ID = '" & Me.txtid & "'"

You should check to see if the recordset is empty unless you have reason to be certain it's not and never will be. You can check with something like:

If rs.EOF And rs.BOF then
   Msgbox "No record found"
   exit sub
End If

The WHERE clause:

Where ID = '" & Me.txtid & "'"

is correct if ID is a text field. If it is a number field then you don't want the single quotes. I'd be like:

Where ID = " & Me.txtid

 

[cstickman]

Sneuberg you are a genius!! That worked like a charm!! Here is how made the changes to the code and the cmbvendorstatus populated.

 Option Compare Database
Option Explicit
  
 Dim db As Database
Dim rs As Recordset
  
 Private Sub cmbvendor_AfterUpdate()
Dim strSQL As String
    
    Set db = CurrentDb
    'Set rs = strSQL
     strSQL = "Select * from tblcontacts Where ID = " & Me.txtid & ""
    
    Set rs = db.OpenRecordset(strSQL)
    
    If Not rs.BOF And Not rs.EOF Then
    Me.cmbvendorstatus = rs!vendorstatus
    End If
    
End Sub

 

[sneuberg]

Me.cmbvendorstatus is not going to be updatable without a lot more code. If you only want to display it then Dlookup is generally used for this. You could do the same thing with just:

Me.cmbvendorstatus = DLookup("[vendorstatus]", "[tblcontacts]", "ID = " & Me.txtid)

Of course learning a little about recordsets isn't a total waste of time.

 

[cstickman]

This is the code that I have for updating the record:

 Private Sub cmdupdate_Click()
     rs.Edit
    rs!vendorstatus = Me.cmbvendorstatus
    rs!FirmShort = Me.txtfirmshort
    rs!FirmLong = Me.txtnewvendor
    rs!Contacts = Me.txtcontacts
    rs!AuditAddress = Me.txtaddress
    rs!AuditCity = Me.txtcity
    rs!AuditState = Me.txtstate
    rs!AuditZip = Me.txtzip
    rs!Emails = Me.txtemail
    rs!Phoneone = Me.txtphoneone
    rs!Phonetwo = Me.txtphonetwo
    rs!StatesLicensed = Me.txtstates
    rs!SecondaryContacts = Me.txtseccontacts
    rs!SecondaryEmails = Me.txtsecemails
    rs!TRAKemails = Me.txttrakemails
    rs!WWRemails = Me.txtweltemails
    rs!Zwickeremails = Me.txtzwickeremail
    rs!AccessType = Me.txtaccesstype
    rs!SystemType = Me.txtsystemtype
    rs.Update
    
    MsgBox "Change Request Record Saved", vbOKOnly
    
    cmdclear_Click
    
End Sub

Am I on the right track or do I need to any more to it?

 

[sneuberg]

By the way

Not rs.BOF And Not rs.EOF

is not the opposite of

rs.BOF And rs.EOF

To test to see if there are records it would be

Not (rs.BOF And Not rs.EOF)

or

Not rs.BOF Or Not rs.EOF

 

[sneuberg]

This is the code that I have for updating the record:

 Private Sub cmdupdate_Click()
     rs.Edit
    rs!vendorstatus = Me.cmbvendorstatus
    rs!FirmShort = Me.txtfirmshort
    rs!FirmLong = Me.txtnewvendor
    rs!Contacts = Me.txtcontacts
    rs!AuditAddress = Me.txtaddress
    rs!AuditCity = Me.txtcity
    rs!AuditState = Me.txtstate
    rs!AuditZip = Me.txtzip
    rs!Emails = Me.txtemail
    rs!Phoneone = Me.txtphoneone
    rs!Phonetwo = Me.txtphonetwo
    rs!StatesLicensed = Me.txtstates
    rs!SecondaryContacts = Me.txtseccontacts
    rs!SecondaryEmails = Me.txtsecemails
    rs!TRAKemails = Me.txttrakemails
    rs!WWRemails = Me.txtweltemails
    rs!Zwickeremails = Me.txtzwickeremail
    rs!AccessType = Me.txtaccesstype
    rs!SystemType = Me.txtsystemtype
    rs.Update
    
    MsgBox "Change Request Record Saved", vbOKOnly
    
    cmdclear_Click
    
End Sub

Am I on the right track or do I need to any more to it?

Looks like the right track if you want to do it that way. What do you have against a bound form. AFW Forum member pbaldy has some thoughts on the matter here. I suggest you read this before you proceed. Maintaining an unbound form is a lot of work. I think you should have a good reason to do it that way.

 

[cstickman]

I took over this project and they went with unbound forms. The guys who actually had the project were programmers and do this day in and out. I was just trying to match what they did in case it goes back to them. If I end up keeping this whole project (I hope not) then I will probably switch everything over to bound forms. Right now I just wanted to keep it uniformed.

Any advice on how to add a new record? I was thinking I would have to open the recordset since it would not be open if they are not selecting from the combo box and use the rs.addnew command?

 

[sneuberg]

Sorry but I've never used recordsets for that so the only thing I'd say is to check the example code here. How did they do it in the rest of the project?

 

[sneuberg]

One more thing. If you want to work with DAO I suggest putting that in your declarations, e.g.,

Dim db As DAO.Database
Dim rs As DAO.Recordset

Somewhere I read that if you don't add the DAO it will start doing ADO things on you in some places.

 

[cstickman]

They used an INSERT INTO command, but with the article you gave me I was able to figure it out with some trial and error. Below is the code and as I assumed you have to open the recordset because they are not choosing the name from the combo box. I also discovered the hard way that you have to requery the combo box so the newly added name will appear.

I add your suggestion about DAO.database & DAO.recordset. Below is the code that works for the add button:

 Dim strSQL As String
  
     Set db = CurrentDb
     strSQL = "Select * from tblcontacts"
    
    Set rs = db.OpenRecordset(strSQL)
    
    rs.AddNew
    rs!vendorstatus = Me.cmbvendorstatus
    rs!FirmShort = Me.txtfirmshort
    rs!FirmLong = Me.txtnewvendor
    rs!Contacts = Me.txtcontacts
    rs!AuditAddress = Me.txtaddress
    rs!AuditCity = Me.txtcity
    rs!AuditState = Me.txtstate
    rs!AuditZip = Me.txtzip
    rs!Emails = Me.txtemail
    rs!Phoneone = Me.txtphoneone
    rs!phonetwo = Me.txtphonetwo
    rs!StatesLicensed = Me.txtstates
    rs!SecondaryContacts = Me.txtseccontacts
    rs!SecondaryEmails = Me.txtsecemails
    rs!AccessType = Me.txtaccesstype
    rs!SystemType = Me.txtsystemtype
    rs.Update
    
    Me.cmbvendor.Requery
    
    MsgBox "Contact added successfully", vbOKOnly
    
    cmdclear_Click
    
End Sub

The SQL statement also changed to just open the database. I think I am going to go back and add Set db = nothing & Set rs = nothing.

Thanks for all of your help!!

 

[sneuberg]

Glad to hear it's going well. This is probably easier to maintain (e.g. add/delete a field) than an INSERT INTO command.

 

[apr pillai]

Populating a Combobox or List box with different set of information dynamically with the click of a button is interesting to try out. The following link quotes the code taken from Microsoft Access Help documents and explains how work with it.

Dynamic ListBox/Combobox Contents