Text Box Expression Builder with Queries

[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


Thanks in advance for your help!

[blacksaibot]

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

Okay this thing keeps giving me some crap about "you must save the field before you execute" for the setfocus part.

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

[RayMilhon]

Each combo box has on On Change event. You can use it as follows:

Private sub combo1_change()
'Assuming combo2 is based on a query with combo1 in the where clause
combo2.requery
'Otherwise
combo2.recordsource = "Select something from sometable where a field = """ & combo1.value & """ "
combo2.requery

End sub

[blacksaibot]

Each combo box has on On Change event. You can use it as follows:

Private sub combo1_change()
'Assuming combo2 is based on a query with combo1 in the where clause
combo2.requery
'Otherwise
combo2.recordsource = "Select something from sometable where a field = """ & combo1.value & """ "
combo2.requery

End sub

I understand what you're trying to tell me... but on my form I only have ONE combo box which will determine the contents of multiple TEXT boxes.

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

[RayMilhon]

Ok, so let me ask a question the query that fills out the textboxes (If I'm reading this correctly) returns 1 record with multiple fields and the fields are assigned to different text boxes.

Assuming that is correct then you need to add the following at line 14 in your code

If rs.eof then
textbox1 = ""
textbox2 = ""
Else
textbox1 = rs!field1
textbox2 = rs!field2
End if

[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??

[blacksaibot]

Here is ALL the code I have in my Code Builder

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

And now, 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"

[RayMilhon]

First thing to do is under Option Compare Database put Option Explicit This requires you to dim any variables prior to use. Then do a compile. That should highlight the error and show you exactly which line it is.