How to check if record is there

[Guest]

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS & ""))
Then

Since I'm not really using dlookup here for what it is meant for, is there a
better (shorter) way to do this?

Thanks,
John

 

[Jeff Boyce]

John

You've asked a very specific "how to" question.

Now let me ask "why?" As in "why do you want to know if the record already
exists?" What business need are you satisfying for you user(s) by looking
for an existing record (by RecordID, yet?!)?

I don't have enough information to tell, but it sounds like there's a chance
you could be using a combobox to let the user look up existing records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks for your reponse. I'll make myself clearer.

In a form I've made a 'quick-search' field where users can enter an
ID-number and press enter, to save them from having to use Ctrl-F all the
time. Before really locating the value I want to make sure the record is
there. I found that Dlookup is more code efficient than a recordset. The
code is working well but I'd like to know if I can make it better.

Private Sub fldQS_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Me.Refresh 'make sure the entered value is stored so it can be processed
If IsNumeric(fldQS) And fldQS > 0 Then
If IsNull(DLookup("[ID]", "Addresses", "[ID_address] = " & fldQS &
"")) Then
MsgBox "ID-number " & fldQS & " not found."
Else
Me.Recordset.FindFirst "ID_address = " & fldQS
End If
Else
MsgBox "Make sure to enter a positive number."
End If
fldQS = Null
End If
End Sub

John

John

You've asked a very specific "how to" question.

Now let me ask "why?" As in "why do you want to know if the record
already exists?" What business need are you satisfying for you user(s) by
looking for an existing record (by RecordID, yet?!)?

I don't have enough information to tell, but it sounds like there's a
chance you could be using a combobox to let the user look up existing
records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS &
"")) Then

Since I'm not really using dlookup here for what it is meant for, is
there a better (shorter) way to do this?

Thanks,
John

 

[Jeff Boyce]

So, if you don't want to use DLookup, you could "roll your own" creation of
a recordset that consisted only of the rowID, then test the number of
records returned. I haven't tested this approach vs. the DLookup, so I
don't know which would be faster.

So you figure your users would know/remember an ID-number better than a
person's name, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reponse. I'll make myself clearer.

In a form I've made a 'quick-search' field where users can enter an
ID-number and press enter, to save them from having to use Ctrl-F all the
time. Before really locating the value I want to make sure the record is
there. I found that Dlookup is more code efficient than a recordset. The
code is working well but I'd like to know if I can make it better.

Private Sub fldQS_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Me.Refresh 'make sure the entered value is stored so it can be
processed
If IsNumeric(fldQS) And fldQS > 0 Then
If IsNull(DLookup("[ID]", "Addresses", "[ID_address] = " & fldQS &
"")) Then
MsgBox "ID-number " & fldQS & " not found."
Else
Me.Recordset.FindFirst "ID_address = " & fldQS
End If
Else
MsgBox "Make sure to enter a positive number."
End If
fldQS = Null
End If
End Sub

John

John

You've asked a very specific "how to" question.

Now let me ask "why?" As in "why do you want to know if the record
already exists?" What business need are you satisfying for you user(s)
by looking for an existing record (by RecordID, yet?!)?

I don't have enough information to tell, but it sounds like there's a
chance you could be using a combobox to let the user look up existing
records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS &
"")) Then

Since I'm not really using dlookup here for what it is meant for, is
there a better (shorter) way to do this?

Thanks,
John

 

[John W. Vinson]

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS & ""))
Then

Since I'm not really using dlookup here for what it is meant for, is there a
better (shorter) way to do this?

Thanks,
John

That's certainly the simplest way. The other is to attempt to open a
recordset: takes more code but might be faster executing. Try

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT ID_Number FROM Addressen WHERE [ID_Number] = " & fldQS)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
<that ID is not in the table>
Else
<it is in the table>
End If
rs.Close
Set rs = Nothing

If ID_Number is a Text field include the syntactic delimiters:

"SELECT ID_Number FROM Addressen WHERE [ID_Number] = '" & fldQS & "'")

Your appending an empty string "" does absolutely nothing, by the way!


John W. Vinson [MVP]

 

[Guest]

It's not like I don't want to use Dlookup, I was wondering if there was a
better way to do this.

I don't understand what you mean by 'your own creation of a record set'. You
mean with a query?

In answer to your retorical question: I'm going to use this extra feature in
a request database. The ID number is used in all correspondence related to
the requests, so a lot of searches by our users are done on the ID-number.
Also every 2 weeks a report is printed to check the status of the requests.
With the ID number on the report they can easily bring the right request to
their screen. Of course users can use the normal search option to search for
names etc., which is also done a lot.

Btw: is this the tone you normally write in? I don't like it.

John

So, if you don't want to use DLookup, you could "roll your own" creation
of a recordset that consisted only of the rowID, then test the number of
records returned. I haven't tested this approach vs. the DLookup, so I
don't know which would be faster.

So you figure your users would know/remember an ID-number better than a
person's name, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reponse. I'll make myself clearer.

In a form I've made a 'quick-search' field where users can enter an
ID-number and press enter, to save them from having to use Ctrl-F all the
time. Before really locating the value I want to make sure the record is
there. I found that Dlookup is more code efficient than a recordset. The
code is working well but I'd like to know if I can make it better.

Private Sub fldQS_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Me.Refresh 'make sure the entered value is stored so it can be
processed
If IsNumeric(fldQS) And fldQS > 0 Then
If IsNull(DLookup("[ID]", "Addresses", "[ID_address] = " & fldQS &
"")) Then
MsgBox "ID-number " & fldQS & " not found."
Else
Me.Recordset.FindFirst "ID_address = " & fldQS
End If
Else
MsgBox "Make sure to enter a positive number."
End If
fldQS = Null
End If
End Sub

John

John

You've asked a very specific "how to" question.

Now let me ask "why?" As in "why do you want to know if the record
already exists?" What business need are you satisfying for you user(s)
by looking for an existing record (by RecordID, yet?!)?

I don't have enough information to tell, but it sounds like there's a
chance you could be using a combobox to let the user look up existing
records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS &
"")) Then

Since I'm not really using dlookup here for what it is meant for, is
there a better (shorter) way to do this?

Thanks,
John

 

[Guest]

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS &
""))
Then

Since I'm not really using dlookup here for what it is meant for, is there
a
better (shorter) way to do this?

Thanks,
John

That's certainly the simplest way. The other is to attempt to open a
recordset: takes more code but might be faster executing. Try

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT ID_Number FROM Addressen WHERE [ID_Number] = " & fldQS)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
If rs.RecordCount = 0 Then
<that ID is not in the table>
Else
<it is in the table>
End If
rs.Close
Set rs = Nothing

If ID_Number is a Text field include the syntactic delimiters:

"SELECT ID_Number FROM Addressen WHERE [ID_Number] = '" & fldQS & "'")

Your appending an empty string "" does absolutely nothing, by the way!

With a small amout of data I notice no difference. I will check later with
larger amounts of data.

Thanks for the pointer about the "". I will remove them.
John

 

[John W. Vinson]

In a form I've made a 'quick-search' field where users can enter an
ID-number and press enter

PMFJI but...

Rather than making the user type in an ID and then telling them that they got
it wrong, why not put a Combo Box on the form to allow them to *select* an
existing ID? That way you don't need any test at all, and the user can simply
start typing in the combo and have Autocomplete jump to the desired ID
directly.

John W. Vinson [MVP]

 

[Guest]

PMFJI but...

Rather than making the user type in an ID and then telling them that they
got
it wrong, why not put a Combo Box on the form to allow them to *select* an
existing ID? That way you don't need any test at all, and the user can
simply
start typing in the combo and have Autocomplete jump to the desired ID
directly.

Good idea! Thank you.

Then I would have to refresh the list periodically to make sure newly added
records are included. What event would be best for that? I guess the Onfocus
of the combo?
I'll start fiddle with it.

John

 

[John W. Vinson]

Then I would have to refresh the list periodically to make sure newly added
records are included. What event would be best for that? I guess the Onfocus
of the combo?

If the standard every 60 seconds refresh is too slow for you, sure. I've never
found it necessary.

John W. Vinson [MVP]

 

[Jeff Boyce]

Sorry if you took umbrage at my response -- I was looking for more
information so I could offer more specific suggestions.

Since a query is (underneath) just a SQL statement, one way to "roll your
own" would be to create a query that gets just one ID-number, then copy the
SQL into a procedure. Using that SQL statement to open a recordset, you
could (as suggested earlier) count the number of records. You'd still need
to prompt for an ID-number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It's not like I don't want to use Dlookup, I was wondering if there was a
better way to do this.

I don't understand what you mean by 'your own creation of a record set'.
You mean with a query?

In answer to your retorical question: I'm going to use this extra feature
in a request database. The ID number is used in all correspondence related
to the requests, so a lot of searches by our users are done on the
ID-number. Also every 2 weeks a report is printed to check the status of
the requests. With the ID number on the report they can easily bring the
right request to their screen. Of course users can use the normal search
option to search for names etc., which is also done a lot.

Btw: is this the tone you normally write in? I don't like it.

John

So, if you don't want to use DLookup, you could "roll your own" creation
of a recordset that consisted only of the rowID, then test the number of
records returned. I haven't tested this approach vs. the DLookup, so I
don't know which would be faster.

So you figure your users would know/remember an ID-number better than a
person's name, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reponse. I'll make myself clearer.

In a form I've made a 'quick-search' field where users can enter an
ID-number and press enter, to save them from having to use Ctrl-F all
the time. Before really locating the value I want to make sure the
record is there. I found that Dlookup is more code efficient than a
recordset. The code is working well but I'd like to know if I can make
it better.

Private Sub fldQS_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Me.Refresh 'make sure the entered value is stored so it can be
processed
If IsNumeric(fldQS) And fldQS > 0 Then
If IsNull(DLookup("[ID]", "Addresses", "[ID_address] = " & fldQS
& "")) Then
MsgBox "ID-number " & fldQS & " not found."
Else
Me.Recordset.FindFirst "ID_address = " & fldQS
End If
Else
MsgBox "Make sure to enter a positive number."
End If
fldQS = Null
End If
End Sub

John

"Jeff Boyce" <[email protected]> schreef in bericht
John

You've asked a very specific "how to" question.

Now let me ask "why?" As in "why do you want to know if the record
already exists?" What business need are you satisfying for you user(s)
by looking for an existing record (by RecordID, yet?!)?

I don't have enough information to tell, but it sounds like there's a
chance you could be using a combobox to let the user look up existing
records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS
& "")) Then

Since I'm not really using dlookup here for what it is meant for, is
there a better (shorter) way to do this?

Thanks,
John

 

[Jeff Boyce]

Thanks FJI, John. I had thought that was what I was suggesting but it would
appear that didn't come through in my response...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I overreacted. Thanks for your explanation.

John

Sorry if you took umbrage at my response -- I was looking for more
information so I could offer more specific suggestions.

Since a query is (underneath) just a SQL statement, one way to "roll your
own" would be to create a query that gets just one ID-number, then copy
the SQL into a procedure. Using that SQL statement to open a recordset,
you could (as suggested earlier) count the number of records. You'd still
need to prompt for an ID-number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

It's not like I don't want to use Dlookup, I was wondering if there was a
better way to do this.

I don't understand what you mean by 'your own creation of a record set'.
You mean with a query?

In answer to your retorical question: I'm going to use this extra feature
in a request database. The ID number is used in all correspondence
related to the requests, so a lot of searches by our users are done on
the ID-number. Also every 2 weeks a report is printed to check the status
of the requests. With the ID number on the report they can easily bring
the right request to their screen. Of course users can use the normal
search option to search for names etc., which is also done a lot.

Btw: is this the tone you normally write in? I don't like it.

John

So, if you don't want to use DLookup, you could "roll your own" creation
of a recordset that consisted only of the rowID, then test the number of
records returned. I haven't tested this approach vs. the DLookup, so I
don't know which would be faster.

So you figure your users would know/remember an ID-number better than a
person's name, or ...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thanks for your reponse. I'll make myself clearer.

In a form I've made a 'quick-search' field where users can enter an
ID-number and press enter, to save them from having to use Ctrl-F all
the time. Before really locating the value I want to make sure the
record is there. I found that Dlookup is more code efficient than a
recordset. The code is working well but I'd like to know if I can make
it better.

Private Sub fldQS_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = 13 Then
Me.Refresh 'make sure the entered value is stored so it can be
processed
If IsNumeric(fldQS) And fldQS > 0 Then
If IsNull(DLookup("[ID]", "Addresses", "[ID_address] = " & fldQS
& "")) Then
MsgBox "ID-number " & fldQS & " not found."
Else
Me.Recordset.FindFirst "ID_address = " & fldQS
End If
Else
MsgBox "Make sure to enter a positive number."
End If
fldQS = Null
End If
End Sub

John

"Jeff Boyce" <[email protected]> schreef in bericht
John

You've asked a very specific "how to" question.

Now let me ask "why?" As in "why do you want to know if the record
already exists?" What business need are you satisfying for you
user(s) by looking for an existing record (by RecordID, yet?!)?

I don't have enough information to tell, but it sounds like there's a
chance you could be using a combobox to let the user look up existing
records...

Regards

Jeff Boyce
Microsoft Office/Access MVP

To check if a record with a certain ID_number is there I now use:

If IsNull(DLookup("[ID_number]", "Adressen", "[ID_number] = " & fldQS
& "")) Then

Since I'm not really using dlookup here for what it is meant for, is
there a better (shorter) way to do this?

Thanks,
John

 

[John W. Vinson]

Thanks FJI, John. I had thought that was what I was suggesting but it would
appear that didn't come through in my response...

You did, briefly anyway, and it evidently didn't... I think he's on the right
track now though.

John W. Vinson [MVP]