Hello everyone,
Please can someone help me with a code to find two(2) fields at thesame time.

As in with a msgbox "Enter Date" then after that "Enter Shift".

Because there are two 'shifts' tied to a 'date'

I will really appreciate this help.

Private Sub Command3_Click()

    Dim rs As New ADODB.Recordset
    Dim cn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    With cn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Data Source") = "\\192.168.0.10\Production Output Report\Production.mdb"
      .Open
    End With
    With cmd
      .ActiveConnection = cn
      .CommandType = adCmdTable
      .CommandText = "RELAXER_PACKAGED"
    End With
    msg = InputBox("ENTER PROD_DATE", "FIND")
    'msgbox(msg)
    Adodc1.Recordset.MoveFirst
    For j = 0 To Adodc1.Recordset.RecordCount - 1
        If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then
            Combo1.Text = Adodc1.Recordset.Fields("Day")
            Text1.Text = Adodc1.Recordset.Fields("Prod_Date")
            Combo2.Text = Adodc1.Recordset.Fields("Shift")
            Text2.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_cartons")
            Text3.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_tonages")
            Text4.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_cartons")
            Text5.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_tonages")
            Text6.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_cartons")
            Text7.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_tonages")
            Text8.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_cartons")
            Text9.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_tonages")
            Text10.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_cartons")
            Text11.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_tonages")
            Text12.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_cartons")
            Text13.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_tonages")
            Text14.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_cartons")
            Text15.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_tonages")
            Text16.Text = Adodc1.Recordset.Fields("Total_no_of_cartons")
            Text17.Text = Adodc1.Recordset.Fields("Total_no_of_tons")
            Rfound = True
            Exit For
        Else
            Rfound = False
        End If
        Adodc1.Recordset.MoveNext
    Next
    If Rfound = False Then
        MsgBox "RECORD DOES NOT EXIST!", vbExclamation
    End If

End Sub

So this code can only locate the "Prod_Date". I want it in a way that after prompting for "Enter Prod_Date" the second msgbox should be "Enter Shift"

Edited 2 Years Ago by Reverend Jim: fixed formatting

Why don't you just show a second one? Like:

Private Sub Form_Load()
Dim msg1, msg2 As String
msg1 = InputBox(prmpt1)
msg2 = InputBox(prmpt2)
Debug.Print (msg1 & "   " & msg2)
End Sub

Sorry please I dont understand what you mean, will appreciate if you can elaborate on the above code

Well you want to prompt for two pieces of input, so use two input boxes.

Dim msg1, msg2 As String
line 16   msg = InputBox("ENTER PROD_DATE", "FIND")
line17    msg1 = InputBox( "Enter Shift", ?)

So you catch the second prompt in msg1

Here is the code below, but is not giving me the required result. Its only giving me the "Prod_Date", the record actually does not exist, that is for where "shift"= "morning". That particular "Prod_Date" has only nigth shift as a record. But at the end I ve that record coming up after entering "morning" for the second inputbox msg of "Enter Shift"

Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "\192.168.0.10\Production Output Report\Production.mdb"
.Open
End With
With cmd
.ActiveConnection = cn
.CommandType = adCmdTable
.CommandText = "RELAXER_PACKAGED"
End With
msg = InputBox("ENTER PROD_DATE", "FIND")
msg1 = InputBox("ENTER SHIFT", "LOCATE")
'msgbox(msg)
Adodc1.Recordset.MoveFirst
For j = 0 To Adodc1.Recordset.RecordCount - 1
If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then
Combo1.Text = Adodc1.Recordset.Fields("Day")
Text1.Text = Adodc1.Recordset.Fields("Prod_Date")
Combo2.Text = Adodc1.Recordset.Fields("Shift")
Text2.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_cartons")
Text3.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_tonages")
Text4.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_cartons")
Text5.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_tonages")
Text6.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_cartons")
Text7.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_tonages")
Text8.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_cartons")
Text9.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_tonages")
Text10.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_cartons")
Text11.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_tonages")
Text12.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_cartons")
Text13.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_tonages")
Text14.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_cartons")
Text15.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_tonages")
Text16.Text = Adodc1.Recordset.Fields("Total_no_of_cartons")
Text17.Text = Adodc1.Recordset.Fields("Total_no_of_tons")
Rfound = True
Exit For
Else
Rfound = False
End If
Adodc1.Recordset.MoveNext
Next
msg1 = InputBox("ENTER SHIFT", "LOCATE")
'msgbox(msg)
Adodc1.Recordset.MoveFirst
For j = 0 To Adodc1.Recordset.RecordCount - 1
If StrComp(Adodc1.Recordset.Fields("Shift"), msg, vbTextCompare) = 0 Then
Combo1.Text = Adodc1.Recordset.Fields("Day")
Text1.Text = Adodc1.Recordset.Fields("Prod_Date")
Combo2.Text = Adodc1.Recordset.Fields("Shift")
Text2.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_cartons")
Text3.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_tonages")
Text4.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_cartons")
Text5.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_tonages")
Text6.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_cartons")
Text7.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_tonages")
Text8.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_cartons")
Text9.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_tonages")
Text10.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_cartons")
Text11.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_tonages")
Text12.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_cartons")
Text13.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_tonages")
Text14.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_cartons")
Text15.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_tonages")
Text16.Text = Adodc1.Recordset.Fields("Total_no_of_cartons")
Text17.Text = Adodc1.Recordset.Fields("Total_no_of_tons")
Rfound = True
Exit For
Else
Rfound = False
End If
Adodc1.Recordset.MoveNext
Next
If Rfound = False Then
MsgBox "RECORD DOES NOT EXIST!", vbExclamation
End If
End Sub

If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then

If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg1, vbTextCompare) = 0 Then

instead of msg shouldn't it be msg1

The find command is not just for Prod_Date only, it should also search for a particular record that matches a "Prod_Date" and a "Shift".

Thanks guys for your concern so far...

Sorry copied the wrong line the first trime. What I tried to point out is that you never use msg1 in your search.

If StrComp(Adodc1.Recordset.Fields("Shift"), msg, vbTextCompare) = 0 Then

I think you wanted to use msg1 here.

Here is the code one more time, after the second inputbox, nothing happens i.e no screen msg indicating whether the record exist or not. Please find the code below:

Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "\192.168.0.10\Production Output Report\Production.mdb"
.Open
End With
With cmd
.ActiveConnection = cn
.CommandType = adCmdTable
.CommandText = "RELAXER_PACKAGED"
End With
rs.Open "SELECT * FROM RELAXER_PACKAGED", cn, adOpenStatic, adLockOptimistic
msg = InputBox("ENTER PROD_DATE", "FIND")
msg = InputBox("ENTER SHIFT", "LOCATE")
'msgbox(msg)
For j = 0 To Adodc1.Recordset.RecordCount - 1
For k = 0 To Adodc1.Recordset.RecordCount - 1
If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then
If StrComp(Adodc1.Recordset.Fields("Shift"), msg, vbTextCompare) = 0 Then
Combo1.Text = Adodc1.Recordset.Fields("Day")
Text1.Text = Adodc1.Recordset.Fields("Prod_Date")
Combo2.Text = Adodc1.Recordset.Fields("Shift")
Text2.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_cartons")
Text3.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_tonages")
Text4.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_cartons")
Text5.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_tonages")
Text6.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_cartons")
Text7.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_tonages")
Text8.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_cartons")
Text9.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_tonages")
Text10.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_cartons")
Text11.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_tonages")
Text12.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_cartons")
Text13.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_tonages")
Text14.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_cartons")
Text15.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_tonages")
Text16.Text = Adodc1.Recordset.Fields("Total_no_of_cartons")
Text17.Text = Adodc1.Recordset.Fields("Total_no_of_tons")
Rfound = True
Exit For
Exit For
Else
Rfound = False
End If
End If
Next
Next
If rs.EOF Then
MsgBox "RECORD DOES NOT EXIST!", vbExclamation
End If
End Sub

O.K. just go back to your original question. You want to use two input boxes to get two values. Look at my original post and then look at your last code.

msg = InputBox("ENTER PROD_DATE", "FIND")
msg = InputBox("ENTER SHIFT", "LOCATE")

So msg ony holds your shift. Then look at your code here:

If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then
If StrComp(Adodc1.Recordset.Fields("Shift"), msg, vbTextCompare) = 0 Then

where you only search for "Shift" because thats what you assingned it as you useonly one variable =msg.

Now this code below is returning "Record Does Not Exist!" message. Even when the querried record actually, does exist. It seems to be working with only the "Else Rfound=False" command.

Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "\192.168.0.10\Production Output Report\Production.mdb"
.Open
End With
With cmd
.ActiveConnection = cn
.CommandType = adCmdTable
.CommandText = "RELAXER_PACKAGED"
End With
msg = InputBox("ENTER PROD_DATE", "FIND")
msg1 = InputBox("ENTER SHIFT", "LOCATE")
Debug.Print (msg & " " & msg1)
'msgbox(msg)
For j = 0 To Adodc1.Recordset.RecordCount - 1
If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then
If StrComp(Adodc1.Recordset.Fields("Shift"), msg1, vbTextCompare) = 0 Then
Combo1.Text = Adodc1.Recordset.Fields("Day")
Text1.Text = Adodc1.Recordset.Fields("Prod_Date")
Combo2.Text = Adodc1.Recordset.Fields("Shift")
Text2.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_cartons")
Text3.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_tonages")
Text4.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_cartons")
Text5.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_tonages")
Text6.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_cartons")
Text7.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_tonages")
Text8.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_cartons")
Text9.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_tonages")
Text10.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_cartons")
Text11.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_tonages")
Text12.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_cartons")
Text13.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_tonages")
Text14.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_cartons")
Text15.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_tonages")
Text16.Text = Adodc1.Recordset.Fields("Total_no_of_cartons")
Text17.Text = Adodc1.Recordset.Fields("Total_no_of_tons")
Rfound = True
Exit For
Else
Rfound = False
End If
End If
Next
MsgBox "RECORD DOES NOT EXIST!", vbExclamation
End Sub

Your msgbox will always display because it is not in any of the conditions but executes within the flow of the program. You need to put it where you want to display the msg. If the rest is working please mark the thread as solved. Thanks

Sorry, what I mean is that the record still doesn't show on the form, as expected, it's returning a null record. Even when such record does exist in the database.

Edited 2 Years Ago by ever-jolly

Hi Mini,
Bingo, here is the final code and its working perfectly. Thanks for your support and concern. I really appreciate...

Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = "\192.168.0.10\Production Output Report\Production.mdb"
.Open
End With
With cmd
.ActiveConnection = cn
.CommandType = adCmdTable
.CommandText = "RELAXER_PACKAGED"
End With
msg = InputBox("ENTER PROD_DATE", "FIND")
msg1 = InputBox("ENTER SHIFT", "LOCATE")
Debug.Print (msg & " " & msg1)
'msgbox(msg)
Adodc1.Recordset.MoveFirst
For j = 0 To Adodc1.Recordset.RecordCount - 1
If StrComp(Adodc1.Recordset.Fields("Prod_Date"), msg, vbTextCompare) = 0 Then
If StrComp(Adodc1.Recordset.Fields("Shift"), msg1, vbTextCompare) = 0 Then
Combo1.Text = Adodc1.Recordset.Fields("Day")
Text1.Text = Adodc1.Recordset.Fields("Prod_Date")
Combo2.Text = Adodc1.Recordset.Fields("Shift")
Text2.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_cartons")
Text3.Text = Adodc1.Recordset.Fields("Ozone relaxer 150g_tonages")
Text4.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_cartons")
Text5.Text = Adodc1.Recordset.Fields("Ozone relaxer 225g_tonages")
Text6.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_cartons")
Text7.Text = Adodc1.Recordset.Fields("Ozone relaxer 400g_tonages")
Text8.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_cartons")
Text9.Text = Adodc1.Recordset.Fields("Ozone relaxer 850g_tonages")
Text10.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_cartons")
Text11.Text = Adodc1.Recordset.Fields("Apple relaxer 150g_tonages")
Text12.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_cartons")
Text13.Text = Adodc1.Recordset.Fields("Apple relaxer 225g_tonages")
Text14.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_cartons")
Text15.Text = Adodc1.Recordset.Fields("Apple relaxer 400g_tonages")
Text16.Text = Adodc1.Recordset.Fields("Total_no_of_cartons")
Text17.Text = Adodc1.Recordset.Fields("Total_no_of_tons")
Rfound = True
Exit For
Else
Rfound = False
End If
End If
Adodc1.Recordset.MoveNext
Next
If Rfound = False Then
MsgBox "RECORD DOES NOT EXIST!", vbExclamation
End If
End Sub

This question has already been answered. Start a new discussion instead.