Find Date and Shift

Question

ever-jolly 0 Newbie Poster

10 Years Ago

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.

visual-basic

2 Contributors
17 Replies
301 Views
5 Days Discussion Span
Latest Post 10 Years Ago Latest Post by ever-jolly

Minimalist 96 Posting Pro

10 Years Ago

Can you show some code so we know where it has to go - buton click, key event ?

Minimalist 96 Posting Pro

10 Years Ago

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

Minimalist 96 Posting Pro

10 Years Ago

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

Minimalist 96 Posting Pro

10 Years Ago

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

Minimalist 96 Posting Pro

10 Years Ago

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.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

ever-jolly 0 Newbie Poster · Answer 1 · 2014-02-03T07:47:29+00:00

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"

ever-jolly 0 Newbie Poster · Answer 2 · 2014-02-03T16:03:29+00:00

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

ever-jolly 0 Newbie Poster · Answer 3 · 2014-02-04T07:41:34+00:00

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

ever-jolly 0 Newbie Poster · Answer 4 · 2014-02-04T15:17:29+00:00

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...

ever-jolly 0 Newbie Poster · Answer 5 · 2014-02-05T07:27:13+00:00

ever-jolly 0 Newbie Poster

10 Years Ago

Yes, I have corrected that

ever-jolly 0 Newbie Poster · Answer 6 · 2014-02-05T11:21:11+00:00

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

Minimalist 96 Posting Pro · Answer 7 · 2014-02-05T13:20:58+00:00

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.

ever-jolly 0 Newbie Poster · Answer 8 · 2014-02-05T14:19:48+00:00

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

Minimalist 96 Posting Pro · Answer 9 · 2014-02-05T15:09:37+00:00

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

ever-jolly 0 Newbie Poster · Answer 10 · 2014-02-05T15:33:09+00:00

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.

Minimalist 96 Posting Pro · Answer 11 · 2014-02-05T17:59:20+00:00

I very much recommend to read through this tutorial beause you are not following procedures of getting the recordset etc.
http://visualbasic.freetutes.com/learn-vb6-advanced/lesson8/p24.html

ever-jolly 0 Newbie Poster · Answer 12 · 2014-02-06T08:33:02+00:00

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