Newbie: How to search for a record using criteria from to two table fields

[Frank Krogh]

I have made a form that displays each field in a table.
In addition I have created two listboxes to let the user select values from
two table fields that together uniqely identifies a record. How do I write
an event procedure that retrieves the record? Is it easier to achieve this
using an index field in the table as a primary key?

I have tried to use the FindFirst command, but it seems to just allow a
search criteria that only includes one field?

Private Sub List1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![List1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks for any suggestions.

Kind regards

Frank Krogh

 

[Frank Stone]

This work for me. I had to create a temp table that
mirrored the real table. the temp table was just for
working with a single record. the sub either updates a
record or adds a new record.
Private Sub cmdAccept_Click()
On Error Resume Next

Dim Dbs_Disc As Database
Dim rsDisc As Recordset
Dim rsNN As Recordset
Dim dbs_tempDisc As Database
Dim rstempDisc As Recordset

If IsNull(Me!txtControlNbr) Then
MsgBox (" Enter a Control Number.")
Exit Sub

Else

If IsNull(Me!txtVendorName) Or IsNull(Me!txtBuyer)
Or IsNull(Me!txtRecdBy) Then
MsgBox ("Not enough data to add as Record")
Exit Sub

Else
If IsNull(Me!txtVendorName) Then
Me!txtVendorName.SetFocus
Exit Sub
Else
If IsNull(Me!txtBuyer) Then
Me!txtBuyer.SetFocus
Exit Sub
Else
If IsNull(Me!txtRecdBy) Then
Me!txtRecdBy.SetFocus
Exit Sub
End If
End If
End If
End If
End If

If Me!txtControlNbr >= DLookup
("Disc_NN.Disc_NxN", "Disc_NN", "") Then
'here i check a table called NN(next number).
'If the number in Me!txtcontrolnbr is the next
'number then add new record else undate
'existing record.
Set Dbs_Disc = CodeDb()

Set rsDisc = Dbs_Disc.OpenRecordset("Disc",
dbOpenDynaset)
'Add a new record
With rsDisc
.AddNew
!dis_ControlNbr = txtControlNbr
!dis_ItemID = txtItemID
!dis_Description = txtDescription
!dis_VendorName = txtVendorName
!dis_POonPackSlip = txtPOonPackSlip
!dis_PackSlipNbr = txtPackSlipNbr
!dis_PackSlipQty = txtPackSlipQty
!dis_POQty = txtPOQty
!dis_OKICountQty = txtOKICountQty
!dis_OverUnderPO = txtOverUnderPO
!dis_OverUnderPS = txtOverUnderPS
!dis_Other = txtOther
!dis_Recdby = txtRecdBy
!dis_DateRecd = txtDateRecd
!dis_Status = txtStatus
!dis_InstructionComments = txtInstructionComments
!dis_Buyer = txtBuyer
!dis_DateDispositioned = txtDateDispositioned
.Update
End With
rsDisc.Close
Dbs_Disc.Close
MsgBox (" Record was added.")
Me.Requery

DoCmd.OpenQuery "qryVMDDelNN", acNormal, acEdit
DoCmd.OpenQuery "qryVMDNewNbr", acNormal, acEdit
'here i use the above 2 queries to delete the old
'next number and add the new next number for the
'next new record.
Call Clear_Form_Click

Else
'edit(update) existing record
Set dbs_tempDisc = CodeDb()

Set rstempDisc = dbs_tempDisc.OpenRecordset
("tempDisc", dbOpenDynaset)
With rstempDisc
.Edit
!dis_ControlNbr = txtControlNbr
!dis_ItemID = txtItemID
!dis_Description = txtDescription
!dis_VendorName = txtVendorName
!dis_POonPackSlip = txtPOonPackSlip
!dis_PackSlipNbr = txtPackSlipNbr
!dis_PackSlipQty = txtPackSlipQty
!dis_POQty = txtPOQty
!dis_OKICountQty = txtOKICountQty
!dis_OverUnderPO = txtOverUnderPO
!dis_OverUnderPS = txtOverUnderPS
!dis_Other = txtOther
!dis_Recdby = txtRecdBy
!dis_DateRecd = txtDateRecd
!dis_Status = txtStatus
!dis_InstructionComments = txtInstructionComments
!dis_Buyer = txtBuyer
!dis_DateDispositioned = txtDateDispositioned
.Update
End With
rsDisc.Close
Dbs_Disc.Close
MsgBox (" Record was Updated.")
Me.Requery
DoCmd.OpenQuery "qryVMDUpdate", acViewNormal,
acEdit 'update query

Call Clear_Form_Click

End If

End Sub

-----Original Message-----
I have made a form that displays each field in a table.
In addition I have created two listboxes to let the user select values from
two table fields that together uniqely identifies a record. How do I write
an event procedure that retrieves the record? Is it easier to achieve this
using an index field in the table as a primary key?

I have tried to use the FindFirst command, but it seems to just allow a
search criteria that only includes one field?

Private Sub List1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![List1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Thanks for any suggestions.

Kind regards

Frank Krogh


.

 

[Marshall Barton]

I have made a form that displays each field in a table.
In addition I have created two listboxes to let the user select values from
two table fields that together uniqely identifies a record. How do I write
an event procedure that retrieves the record? Is it easier to achieve this
using an index field in the table as a primary key?

I have tried to use the FindFirst command, but it seems to just allow a
search criteria that only includes one field?

Private Sub List1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![List1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

You can add additional criteria to the FindFirst expression
by using And or Or. I think this is what you're looking
for:

rs.FindFirst "[field1] = '" & Me![List1] & "' AND [field2]
= '" & Me![List2] & "'"

You may also need to add some more code to check that both
list boxes actually have a selected item and skip the find
when htey don't.

 

[Frank Krogh]

Thanks for helping.

I have tried to modify the FindFirst command, but nothing happends when
executed, and I receive no error message.

rs.FindFirst "[strField1] = '" & Me![List50] & "' AND [numField2]" = " &
Str(Nz(Me![List58], 0))"

I'm not sure I have got the notation right - is there something wrong with
the use of comment signs ( ', " ) in the expression?

Do you know of a sample application where this function is implemented?


Kind regards

Frank Krogh

I have made a form that displays each field in a table.
In addition I have created two listboxes to let the user select values from
two table fields that together uniqely identifies a record. How do I write
an event procedure that retrieves the record? Is it easier to achieve this
using an index field in the table as a primary key?

I have tried to use the FindFirst command, but it seems to just allow a
search criteria that only includes one field?

Private Sub List1_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[field1] = '" & Me![List1] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

You can add additional criteria to the FindFirst expression
by using And or Or. I think this is what you're looking
for:

rs.FindFirst "[field1] = '" & Me![List1] & "' AND [field2]
= '" & Me![List2] & "'"

You may also need to add some more code to check that both
list boxes actually have a selected item and skip the find
when htey don't.

 

[Marshall Barton]

I have tried to modify the FindFirst command, but nothing happends when
executed, and I receive no error message.

rs.FindFirst "[strField1] = '" & Me![List50] & "' AND [numField2]" = " &
Str(Nz(Me![List58], 0))"

I'm not sure I have got the notation right - is there something wrong with
the use of comment signs ( ', " ) in the expression?

Do you know of a sample application where this function is implemented?

This sort of thing is used somewhere in almost every
application. I'm not a big fan of Northwind (on the Access
CD), but I'll bet you can find at least one example there.

Learning the general rules of strings is all you really need
for this. First thing to note is that SQL wants Text field
values to be enclosed in quotes (either single or double)
and numeric type field values not in quotes (Date/Time
values in # signs). The next thing to know is that you can
use two quotes to represent one quote inside a quoted
string.

There are many situations where Access automatically
converts a number (or Date/Time) to a string. This is
almost always a convenience, but can be a pain if Access
makes a bad guess about what is needed.

An important debugging technique that you can use to see how
all this kind of thing works out is to use a MsgBox or
Debug.Print in your code so you can look at the result of
all the concatenation.

Based on your attempt, it seems that field2 is a numeric
field, so your code should be:

Dim strSearch As String
strSearch = "[strField1] = '" & Me![List50] & _
"' AND [numField2] = " & Nz(Me![List58], 0)
MsgBox strSearch ' testing only
rs.FindFirst strSearch