Update text box after selections from 3 combo boxes using query

I have a query that queries the combo boxes by using a form. The user selects all three and I need to fill in the text box automatically.  The Dlookup does not store the field ODOBTX in the database field named as400 narr.  Here is the query I use,  How can I do this being new to access this is difficult.

code:

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


Thank You
anthony777Asked:
Who is Participating?
 
Chuck WoodCommented:
Sorry, Private Sub ODOBNM_AfterUpdate() should be Private Sub cboODOBNM_AfterUpdate()

Chuck
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
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.  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
getting error on this line

TextBoxUpdate
thank you
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
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
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
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
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
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
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
runtime error '-214217904 (80040e10)':

no value given one or more required paramaters.
0
 
Chuck WoodCommented:
Did you move the message box (ADD THIS) to right after the strSQL = "SELECT OBJP1.ODOBTX FROM OBJP1 ... in Public Sub TextBoxUpdate?

Chuck
0
 
anthony777Author Commented:
msg box with the sql string comes up after selecting the first combo box data.

thnk you
0
 
Chuck WoodCommented:
What did the message box say?

Chuck
0
 
anthony777Author Commented:

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
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
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
0
 
Chuck WoodCommented:
What did the message box say?

Chuck
0
 
anthony777Author Commented:
get error syntax wrong in From clause

thank you
0
 
Chuck WoodCommented:
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
0
 
anthony777Author Commented:
objp1 is a table

thank you
0
 
Chuck WoodCommented:
Is the code in my last post the code you have now?

Chuck
0
 
anthony777Author Commented:
yes thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.