Update text box after selections from 3 combo boxes using query

You are close. I believe this is what you are looking for:

SELECT ODOBTX FROM OBJP1 WHERE ODLBNM = '" & Forms!FormName!cboODLBNM & "' AND ODOBNM = '" & Forms!FormName!cboODOBNM & "'" ' (if the combo boxes contain character information)

'  OR

SELECT ODOBTX FROM OBJP1 WHERE ODLBNM = " & Forms!FormName!cboODLBNM & " AND ODOBNM = " & Forms!FormName!cboODOBNM ' (if the combo boxes contain numeric information)

Chuck

Ok the query works I just cannot get the query itself to show up on the forms textbox automatically.  Then I need to make sure it writes the data displayed to the table.  

OK, try this:

' in the combo box after update event
Private Sub cboODLBNM_AfterUpdate()
    ' if the combo boxes both have data,
    If Not Isnull(cboODLBNM) And Len(Trim(cboODLBNM)) > 0 And _
      Not Isnull(ODOBNM) And Len(Trim(ODOBNM)) > 0 Then
        ' update the text box
        TextBoxUpdate
End Sub

' in the combo box after update event
Private Sub ODOBNM_AfterUpdate()
    ' if the combo boxes both have data,
    If Not Isnull(cboODLBNM) And Len(Trim(cboODLBNM)) > 0 And _
      Not Isnull(ODOBNM) And Len(Trim(ODOBNM)) > 0 Then
        ' update the text box
        TextBoxUpdate
End Sub

Private Sub TextBoxUpdate()
    Dim cnn As ADODB.Connection
    Set cnn = Currentproject.Connection
    Dim strSQL As String
    strSQL="SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=[Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=[Forms]![Object]![cboODOBNM]));"
    Dim rst As New ADODB.Recordset
    With rst
        .Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
        If Not .EOF Then
            txtTextBox = .Fields(0) ' (substitute the name of your text box for txtTextBox)
            Me.Dirty = False
        End If
        .Close
    End With
    Set rst = Nothing
End Sub

Chuck

getting error on this line

TextBoxUpdate
thank you

Did you put the Private Sub TextBoxUpdate in the same form as the combo boxes (probably at the end)?

If not, you need to put it in a module with Public instead of Private.

Chcuk

The textfield on the form does not show up with any data just blank in the field in the database is also blank.

Thank You

OK. Try this:

Public Sub TextBoxUpdate()
    Dim cnn As ADODB.Connection
    Set cnn = Currentproject.Connection
    Dim strSQL As String
    strSQL="SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=[Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=[Forms]![Object]![cboODOBNM]));"
    Dim rst As New ADODB.Recordset
    With rst
        .Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
'===========================
' ADD THIS
MsgBox "End Of File is:" & .EOF
'===========================
        If Not .EOF Then
            txtTextBox = .Fields(0) ' (substitute the name of your text box for txtTextBox)
            Me.Dirty = False
        End If
        .Close
    End With
    Set rst = Nothing
End Sub

Run it and let me know what message the message box shows.

Chuck

no msg box showed up when using the form here is the code I did

Public Sub TextBoxUpdate()
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim strSQL As String
    strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=[Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=[Forms]![Object]![cboODOBNM]));"
    Dim rst As New ADODB.Recordset
    With rst
        .Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
'===========================
' ADD THIS
MsgBox "End Of File is:" & .EOF
'===========================
        If Not .EOF Then
       as400narr = .Fields(0) ' (substitute the name of your text box for txtTextBox)
            Me.Dirty = False
        End If
        .Close
    End With
    Set rst = Nothing
End Sub

When you created the Private Sub cboODLBNM_AfterUpdate and Private Sub ODOBNM_AfterUpdate, did you go into the combo box properties and set the After Update event property to [Event Procedure] and click on the ellipsis (...) next to it to tie the event to the code? If not, do this on both combo box properties.

Did you select a new value in either of the combo boxes?
Did you have a value in both combo boxes?

Chuck

get the following error now.
Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly and it highlights this field:


error reads;  No value given for one or more required paramaters

thank you

OK. Change Public Sub TextBoxUpdate to this:

Public Sub TextBoxUpdate()
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim strSQL As String
    strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=[Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=[Forms]![Object]![cboODOBNM]));"
'===========================
' ADD THIS
MsgBox "strSQL:" & strSQL
'===========================
    Dim rst As New ADODB.Recordset
    With rst
        .Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
        If Not .EOF Then
       as400narr = .Fields(0) ' (substitute the name of your text box for txtTextBox)
            Me.Dirty = False
        End If
        .Close
    End With
    Set rst = Nothing
End Sub

What message do you get now?

Chuck

runtime error '-214217904 (80040e10)':

no value given one or more required paramaters.

Did you move the message box (ADD THIS) to right after the strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 ... in Public Sub TextBoxUpdate?

Chuck

msg box with the sql string comes up after selecting the first combo box data.

thnk you

What did the message box say?

Chuck

this happens after selection for first combo box

strSql-Select OBJP1.ODOBTX from OBJP1 WHERE (((OBJP!.ODLBNM)=[Forms]![Object]![cboODLBNM]) and ((OBJP!.ODOBNM)=[Forms]![Object]![cboODOBNM]));

then hit ok on message it say debug then points to


        .Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

thank you

OK. Change:

strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=[Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=[Forms]![Object]![cboODOBNM]));"

TO:

strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=" & [Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=" & [Forms]![Object]![cboODOBNM] & "));"

And try it again.

Chuck

it seems like all three combos have to be selected before the text can be displayed this is still giving me sql error after the first combo please help

What did the message box say?

Chuck

get error syntax wrong in From clause

thank you

OK. You should have this code now:

Public Sub TextBoxUpdate()
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim strSQL As String
    strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 " & _
        "WHERE (((OBJP1.ODLBNM)=" & [Forms]![Object]![cboODLBNM]) " & _
        "AND ((OBJP1.ODOBNM)=" & [Forms]![Object]![cboODOBNM] & "));"
    Dim rst As New ADODB.Recordset
    With rst
        .Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly
        If Not .EOF Then
       as400narr = .Fields(0) ' (substitute the name of your text box for txtTextBox)
            Me.Dirty = False
        End If
        .Close
    End With
    Set rst = Nothing
End Sub

1. Is this the code you have now?
2. Can you confirm that OBJP1 is a table or query?

Chuck

objp1 is a table

thank you

Is the code in my last post the code you have now?

Chuck

yes thank you