How to find a record

P

pokdbz

I need to know how to find a record using code? Basicly I want to search a
field say GeneticIdNumber to see if the currentGeneticIdNumber is in the
GeneticIdNumber field.
 
P

pokdbz

I am always going to the "match" part of the if statement no matter what.
The field I want to check is geneticIDnumber in the DB to see if it =
RandomNum if it does they I want it to display "Match".

Do I have this setup right?

Me.geneticIDnumber = RandomNum
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum
If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
End If
 
A

Allen Browne

Okay, so it finds a match.

Now set the Bookmark of the form to the bookmark of the clone set:
Me.Bookmark = rs.Bookmark
That sets the current record of the form to the one found in the clone set.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

pokdbz said:
I am always going to the "match" part of the if statement no matter what.
The field I want to check is geneticIDnumber in the DB to see if it =
RandomNum if it does they I want it to display "Match".

Do I have this setup right?

Me.geneticIDnumber = RandomNum
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum
If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
End If

Allen Browne said:
Use FindFirst on the RecordsetClone of the form.
Test NoMatch, and set the form's Bookmark.

For an example, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
Click to expand...
Click to expand...
 
P

pokdbz

Actually it says that it finds a match everytime. Even if there is no match
it says that it is finding one. I don't need to display anything on the
form. I just need to know if there is a match or not.

Allen Browne said:
Okay, so it finds a match.

Now set the Bookmark of the form to the bookmark of the clone set:
Me.Bookmark = rs.Bookmark
That sets the current record of the form to the one found in the clone set.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

pokdbz said:
I am always going to the "match" part of the if statement no matter what.
The field I want to check is geneticIDnumber in the DB to see if it =
RandomNum if it does they I want it to display "Match".

Do I have this setup right?

Me.geneticIDnumber = RandomNum
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum
If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
End If

Allen Browne said:
Use FindFirst on the RecordsetClone of the form.
Test NoMatch, and set the form's Bookmark.

For an example, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html


I need to know how to find a record using code? Basicly I want to
search a
field say GeneticIdNumber to see if the currentGeneticIdNumber is in
the
GeneticIdNumber field.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Guess I don't understand your code.

On the first line, you set the value of the field geneticIDnumber to the
value of RandomNumber.

Then you save the record.

Then you try to find that value.

Well, you just saved it, so it would not be surprising if it now finds it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

pokdbz said:
Actually it says that it finds a match everytime. Even if there is no
match
it says that it is finding one. I don't need to display anything on the
form. I just need to know if there is a match or not.

Allen Browne said:
Okay, so it finds a match.

Now set the Bookmark of the form to the bookmark of the clone set:
Me.Bookmark = rs.Bookmark
That sets the current record of the form to the one found in the clone
set.


pokdbz said:
I am always going to the "match" part of the if statement no matter
what.
The field I want to check is geneticIDnumber in the DB to see if it =
RandomNum if it does they I want it to display "Match".

Do I have this setup right?

Me.geneticIDnumber = RandomNum
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum
If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
End If

:

Use FindFirst on the RecordsetClone of the form.
Test NoMatch, and set the form's Bookmark.

For an example, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html


I need to know how to find a record using code? Basicly I want to
search a
field say GeneticIdNumber to see if the currentGeneticIdNumber is in
the
GeneticIdNumber field.
Click to expand...
Click to expand...
Click to expand...
 
P

pokdbz

Does the Dirty save the record?


Allen Browne said:
Guess I don't understand your code.

On the first line, you set the value of the field geneticIDnumber to the
value of RandomNumber.

Then you save the record.

Then you try to find that value.

Well, you just saved it, so it would not be surprising if it now finds it?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

pokdbz said:
Actually it says that it finds a match everytime. Even if there is no
match
it says that it is finding one. I don't need to display anything on the
form. I just need to know if there is a match or not.

Allen Browne said:
Okay, so it finds a match.

Now set the Bookmark of the form to the bookmark of the clone set:
Me.Bookmark = rs.Bookmark
That sets the current record of the form to the one found in the clone
set.


I am always going to the "match" part of the if statement no matter
what.
The field I want to check is geneticIDnumber in the DB to see if it =
RandomNum if it does they I want it to display "Match".

Do I have this setup right?

Me.geneticIDnumber = RandomNum
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum
If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
End If

:

Use FindFirst on the RecordsetClone of the form.
Test NoMatch, and set the form's Bookmark.

For an example, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html


I need to know how to find a record using code? Basicly I want to
search a
field say GeneticIdNumber to see if the currentGeneticIdNumber is in
the
GeneticIdNumber field.
Click to expand...
Click to expand...
Click to expand...
 
P

pokdbz

Here is the code that I have:
What I want it to do is check the field geneticIDnumber in my table to see
if that number was already used. If it was used generate a new
geneticIDnumber, if it wasn't used do nothing.

Do I have this setup right?

Me.geneticIDnumber = RandomNum

Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum

If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
Randomize 'seeds random number
RandomNum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
'restricts random number

End If
 
A

Allen Browne

What is "geneticIDnumber"?
Is it an *unbound* text box (i.e. nothing in its ControlSource)?
If so, the code whould work (assuming geneticIDnumber is a Number type
field, not a Text type field.)

If you want to find out whether the number is already in the table, a
DLookup() might be better, e.g.:
Dim dblRandom As Double
Dim strWhere As String
Dim varResult

dblRandom = Rnd()
strWhere = "geneticIDnumber = " & dblRandom
varResult = DLookup("geneticIDnumber", "Table1", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
MstBox "found"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

pokdbz said:
Here is the code that I have:
What I want it to do is check the field geneticIDnumber in my table to see
if that number was already used. If it was used generate a new
geneticIDnumber, if it wasn't used do nothing.

Do I have this setup right?

Me.geneticIDnumber = RandomNum

Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum

If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
Randomize 'seeds random number
RandomNum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
'restricts random number

End If

Allen Browne said:
Yep.
Click to expand...
Click to expand...
 
P

pokdbz

The DLookup worked perfect. Thanks for the help and being patient.

Allen Browne said:
What is "geneticIDnumber"?
Is it an *unbound* text box (i.e. nothing in its ControlSource)?
If so, the code whould work (assuming geneticIDnumber is a Number type
field, not a Text type field.)

If you want to find out whether the number is already in the table, a
DLookup() might be better, e.g.:
Dim dblRandom As Double
Dim strWhere As String
Dim varResult

dblRandom = Rnd()
strWhere = "geneticIDnumber = " & dblRandom
varResult = DLookup("geneticIDnumber", "Table1", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
MstBox "found"
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

pokdbz said:
Here is the code that I have:
What I want it to do is check the field geneticIDnumber in my table to see
if that number was already used. If it was used generate a new
geneticIDnumber, if it wasn't used do nothing.

Do I have this setup right?

Me.geneticIDnumber = RandomNum

Set rs = Me.RecordsetClone
rs.FindFirst "[geneticIDnumber] = " & RandomNum

If rs.NoMatch Then
MsgBox "No match"
Else
MsgBox "match"
Randomize 'seeds random number
RandomNum = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
'restricts random number

End If

Allen Browne said:
Yep.


Does the Dirty save the record?
Click to expand...
Click to expand...
Click to expand...
 
Top