Queries filling out textbox w/ combobox (1 Viewer)

[blacksaibot]

I know how to do this in VB.NET but VBA is killing me!!!

I have a form with a combo box. I would like to create an "on change" event where it updates the text boxes on that same form depending on what's been selected in the combo box.

I don't know how to change the combo box's selected index so it defaults to the first item so there's no blanks/errors in my form.

So when the combobox contents is changed, I'd like a routine that updates all the text boxes kind of like this:

sub update()
TextBox = "SELECT * FROM Table_Name WHERE ID =" & Combobox.text
end sub

In other words, selecting something from the combox runs a query to grab and fill out textboxes with data from the DB.

I've tried something like this...

Option Compare Database
Private Sub cbx_UserID_BeforeUpdate(Cancel As Integer)
Call updateTextFields
End Sub
Private Sub updateTextFields()
Dim DescrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
cbx_UserID.SetFocus
DescrSQL = "SELECT Surveys.[Duty_Pos] FROM Surveys WHERE Surveys.[User_ID]='" & cbx_UserID.Text & "';"
 
Set db = CurrentDb()
Set rs = db.OpenRecordset(DescrSQL)
 
rs.Close
db.Close
 
End Sub

How do I work with recordsets so I can assign the values from my query results to the other text fields??

Thanks in advance for your help!

 

[carl6885]

Hi

In order to populate the textboxes you would do something like:

me.textbox1 = rs![field1]
me.textbox2 = rs![field2]

you dont need the [] unless the field name has a space but its good practice

to set the defaul combo box value you will need to declare something like:

Me.cboBox.Value = Me.cboBox.Column(0, 0) - 1st 0 being the column, 2nd 0 being the row. so 0,0 return first value in the combo box.

Thanks

Carl

 

[blacksaibot]

This silly thing vies me an error on the following line

cbx_UserID.SetFocus

Run-time error '2108':

You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.

WTF??

 

[vbaInet]

Is the form bound or unbound?

 

[blacksaibot]

Is the form bound or unbound?

I don't even know what that means, sorry.

If you're asking if the combox is bound to a query, then yes. Its values are determined by a query I have in Access.

 

[vbaInet]

All you need to do is filter the form's record source based on the combo box's selection. You do that using the Filter and FilterOn properties of the form.

 

[blacksaibot]

So far this is ALL the code I have


This is ALL the code I have.

Option Compare Database
 
Private Sub Form_Load()
Dim strRowSource As String
strRowSource = "SELECT DISTINCT [q_User_IDs].[User_ID] FROM q_User_IDs ORDER BY [User_ID]"
cbx_UserID.RowSource = strRowSource
    
End Sub

Private Sub cbx_UserID_AfterUpdate(Cancel As Integer)

    If IsNull(Me.cbx_UserID) Then
         Me.cbx_UserID = Me.cbx_UserID.ItemData(0)
     End If
 
Call updateTextFields
End Sub
 

Private Sub updateTextFields()
  
     Dim DescrSQL As String
     Dim db As DAO.Database
     Dim rs As DAO.Recordset
  
  
     DescrSQL = _
         "SELECT Duty_Pos " & _
         "FROM Surveys " & _
         "WHERE User_ID = " & cbx_UserID
  
 
    Set db = CurrentDb()
     Set rs = db.OpenRecordset(DescrSQL)
 
     With rs
          .MoveFirst
          txt_DutyPos = rs!field1
     End With
 
End Sub

that code yields an Error "The expression On Load you entered as the event property produced the following error: Procedure Declaration Does Not Match Description of Event or procedure having the same name"

 

[vbaInet]

I did mention using the Filter and FilterOn properties of the form in my last post. Did you look into those?

 

[carl6885]

Not sure if I am grasping it or my understanding of vba isn't there (I'm by no means an expert) but just because the combo box has a row source doesn't make it bound?? I thought that was if the form was built directly from table etc.

Error is to do with the recordset wanting to save record.

Are you using dbopendynaset in the .open command also have you tried it without the with statement I'd take out the move first too as you shouldn't need it.

See if that helps....

 

[carl6885]

Not sure if I am grasping it or my understanding of vba isn't there (I'm by no means an expert) but just because the combo box has a row source doesn't make it bound?? I thought that was if the form was built directly from table etc.

Error is to do with the recordset wanting to save record.

Are you using dbopendynaset in the .open command also have you tried it without the with statement I'd take out the move first too as you shouldn't need it.

See if that helps....

 

[blacksaibot]

I did mention using the Filter and FilterOn properties of the form in my last post. Did you look into those?

I don't even see that in the properties window. So I just skipped over your post. If I overlooked it, silly assed Microsoft didn't do me the courtesy of listing the controls' properties alpabetically to make it easier to find things.

 

[vbaInet]

You will see them in the Property Sheet of the form itself. They are located in the Data tab and can be progamatically modified.

Me.Filter = "SomeField = " & Me.ComboBox
Me.FilterOn = True

This will cause your form to be filtered based on the selection made in the combo box.