Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Update text box after selections from 3 combo boxes using query

Posted on 2005-04-27
25
Medium Priority
?
236 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:anthony777
  • 13
  • 12
25 Comments
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13875981
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
 

Author Comment

by:anthony777
ID: 13876074
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13876384
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 16

Accepted Solution

by:
Chuck Wood earned 1000 total points
ID: 13876400
Sorry, Private Sub ODOBNM_AfterUpdate() should be Private Sub cboODOBNM_AfterUpdate()

Chuck
0
 

Author Comment

by:anthony777
ID: 13876696
getting error on this line

TextBoxUpdate
thank you
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13876767
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
 

Author Comment

by:anthony777
ID: 13877056
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13877416
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
 

Author Comment

by:anthony777
ID: 13878992
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13879144
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
 

Author Comment

by:anthony777
ID: 13879798
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13879892
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
 

Author Comment

by:anthony777
ID: 13880066
runtime error '-214217904 (80040e10)':

no value given one or more required paramaters.
0
 
LVL 16

Expert Comment

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

Chuck
0
 

Author Comment

by:anthony777
ID: 13880193
msg box with the sql string comes up after selecting the first combo box data.

thnk you
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13880329
What did the message box say?

Chuck
0
 

Author Comment

by:anthony777
ID: 13880446

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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13880477
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
 

Author Comment

by:anthony777
ID: 13882471
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
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13884766
What did the message box say?

Chuck
0
 

Author Comment

by:anthony777
ID: 13917587
get error syntax wrong in From clause

thank you
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13917970
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
 

Author Comment

by:anthony777
ID: 13918047
objp1 is a table

thank you
0
 
LVL 16

Expert Comment

by:Chuck Wood
ID: 13918281
Is the code in my last post the code you have now?

Chuck
0
 

Author Comment

by:anthony777
ID: 13918298
yes thank you
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question