Look Up Reg Number

  • Thread starter Thread starter dbl
  • Start date Start date
D

dbl

Hi
When I enter a Registration number into a field called Registration_Numbers
in my IncidentData form, after update I would like it to check the
registration number against a list in my tbl called tblRegNumbers.
If its there then I would like it to update my field Policy_Number in my
IncidentData form. If its not then it makes no changes.
The Policy Number is also stored in the tbl tblRegNumbers under the name
txtRegPolicyNo

Any help would be much appreciated.

Thanks Bob
 
Bob,

Here's one possible approach...

Dim ExistingReg As Variant
ExistingReg =
DLookup("[txtRegPolicyNo]","tblRegNumbers","[Registration_Number]='" &
Me.Registration_Number & "'")
If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
End If

(I have assumed that Registration_Number is a text data type)
 
Steve when running the following code I have an error as follows

Type declaration character does not match declared data type

it highlights the following part of the code

..Registration_Number&

This is how I entered the code.


Private Sub Registration_Number_AfterUpdate()
Dim ExistingReg As Variant
ExistingReg = DLookup("[txtPolicyNo]", "tblRegNumbers",
"[Registration_Number]" = "" & Me.Registration_Number&, " ")
If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg

End If

End Sub

Any idea's?

Bob


Steve Schapel said:
Bob,

Here's one possible approach...

Dim ExistingReg As Variant
ExistingReg =
DLookup("[txtRegPolicyNo]","tblRegNumbers","[Registration_Number]='" &
Me.Registration_Number & "'")
If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
End If

(I have assumed that Registration_Number is a text data type)

--
Steve Schapel, Microsoft Access MVP

Hi
When I enter a Registration number into a field called Registration_Numbers
in my IncidentData form, after update I would like it to check the
registration number against a list in my tbl called tblRegNumbers.
If its there then I would like it to update my field Policy_Number in my
IncidentData form. If its not then it makes no changes.
The Policy Number is also stored in the tbl tblRegNumbers under the name
txtRegPolicyNo

Any help would be much appreciated.

Thanks Bob
Click to expand...
Click to expand...
 
Steve when I run the following code it gives an error as follows:
Type declaration character does not match declared data type.

It then highlights the following part of the code.

..Registration_Number&,

This is how I entered the code.

Private Sub Registration_Number_AfterUpdate()
Dim ExistingReg As Variant
ExistingReg = DLookup("[txtPolicyNo]", "tblRegNumbers",
"[Registration_Number]" = "" & Me.Registration_Number&, " ")
If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg

End If

End Sub

Any idea's?

Bob
Steve Schapel said:
Bob,

Here's one possible approach...

Dim ExistingReg As Variant
ExistingReg =
DLookup("[txtRegPolicyNo]","tblRegNumbers","[Registration_Number]='" &
Me.Registration_Number & "'")
If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
End If

(I have assumed that Registration_Number is a text data type)

--
Steve Schapel, Microsoft Access MVP

Hi
When I enter a Registration number into a field called Registration_Numbers
in my IncidentData form, after update I would like it to check the
registration number against a list in my tbl called tblRegNumbers.
If its there then I would like it to update my field Policy_Number in my
IncidentData form. If its not then it makes no changes.
The Policy Number is also stored in the tbl tblRegNumbers under the name
txtRegPolicyNo

Any help would be much appreciated.

Thanks Bob
Click to expand...
Click to expand...
 
Bob,

If you look carefully at this part of the code in the example I gave...

"[Registration_Number]='" & Me.Registration_Number & "'"

.... and compare it with what you put...

"[Registration_Number]" = "" & Me.Registration_Number&, " "

.... you will see a number of significant differences. If you correct
these errors in your version, I think the problem will be solved.

Please post back if you still can't get it.
 
Steve can you confirm that it is the following equals apostrophe then speech
marks and at the end speech marks apostrophe then speech marks?

As below

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[Registration_Number]='" & Me.Registration_Number&"'")

If that's right then the line is highlighted in red and the following error
appears Expected: List separator or) and it also highlights the speech marks
at the end.

Thanks Bob


Steve Schapel said:
Bob,

If you look carefully at this part of the code in the example I gave...

"[Registration_Number]='" & Me.Registration_Number & "'"

... and compare it with what you put...

"[Registration_Number]" = "" & Me.Registration_Number&, " "

... you will see a number of significant differences. If you correct
these errors in your version, I think the problem will be solved.

Please post back if you still can't get it.

--
Steve Schapel, Microsoft Access MVP
Steve when running the following code I have an error as follows

Type declaration character does not match declared data type

it highlights the following part of the code

.Registration_Number&

This is how I entered the code.


Private Sub Registration_Number_AfterUpdate()
Dim ExistingReg As Variant
ExistingReg = DLookup("[txtPolicyNo]", "tblRegNumbers",
"[Registration_Number]" = "" & Me.Registration_Number&, " ")
If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg

End If

End Sub

Any idea's?

Bob
Click to expand...
Click to expand...
 
Bob,

Yes, you are correct with your understanding of the order of the single
and double quotes. You will need to put in spaces as per my example,
i.e. ...
Me.Registration_Number & "'"
.... instead of...
Me.Registration_Number&"'"

Also, just to confirm that in your code this is all on one line, not
split onto 2 lines like the word wrap in the newsgroup post puts it to.

Also, as I mentioned earlier, the syntax used in my example assumes that
Registration_Number is a text data type, not a number. Since you didn't
respond to this, I assumed my assumption was correct.
 
Steve in now works but its not picking up the correct data, is this because
the reg numbers are in the following format

AA02ABC
A123BBC
CD52ASD

and so on. they are not sorted in any way should I put them into a qry so
that I can sort them? what do think?

The Reg Field is a text field.

Thanks for patience Bob

Steve Schapel said:
Bob,

Yes, you are correct with your understanding of the order of the single
and double quotes. You will need to put in spaces as per my example,
i.e. ...
Me.Registration_Number & "'"
... instead of...
Me.Registration_Number&"'"

Also, just to confirm that in your code this is all on one line, not
split onto 2 lines like the word wrap in the newsgroup post puts it to.

Also, as I mentioned earlier, the syntax used in my example assumes that
Registration_Number is a text data type, not a number. Since you didn't
respond to this, I assumed my assumption was correct.

--
Steve Schapel, Microsoft Access MVP
Steve can you confirm that it is the following equals apostrophe then speech
marks and at the end speech marks apostrophe then speech marks?

As below

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[Registration_Number]='" & Me.Registration_Number&"'")

If that's right then the line is highlighted in red and the following error
appears Expected: List separator or) and it also highlights the speech marks
at the end.

Thanks Bob
Click to expand...
Click to expand...
 
Bob.

Any sorting should not make any difference. In the tblRegNumbers table,
each Registration_Number only appears once, correct? And each
Registration_Number has an associated txtRegPolicyNo, correct? And then
on the IncidentData form, you enter a Registration_Number. So, what is
supposed to happen then is that Registration_Number is supposed to be
located (if there is one) in the tblRegNumbers table, and the value of
the corresponding txtRegPolicyNo should then be entered into the
Policy_Number for the same record on the form. Do you mean to tell me
that this is not happening? Do you mean to tell me that a Policy_Number
is being shown, but that it is not the txtRegPolicyNo corresponding to
the Registration_Number you entered on the form? Sorry, I am missing
something here, but we'll eventually get it cracked!
 
Steve your description of what happens (or should happen) is correct and
your description of the lay out is also correct.

It always picks up the first Policy Number in the tblRegNumbers regard less
of what reg number is input (assuming of course it is one that's listed in
the table) other wise it makes on changes.

Thanks again for you help I really do appreciate it.

Regards Bob
 
Bob,

I've run out of ideas, I'm afraid. If you would like to zip up an .mdb
and email it to me, I will take a look at it for you. Just remove the
..ns from the end of my reply email address. You don't need to sent the
whole system necessarily, just needs to be the tblRegNumbers table,
whatever table(s)/query the form is based on, and the form.
 
Steve I will resend it now 21.37 if you do not receive it would you please
e-mail me @ (e-mail address removed)
I will reply to the address and hope fully you will receive it.

Thanks Bob
 
Bob,

Thanks, I got the file, and I have spotted the problem... The field
isn't called Registration_Number it's called txtRegNo so the code needs
to refer to the field by its correct name, i.e.

ExistingReg =
DLookup("[txtRegPolicyNo]","tblRegNumbers","[txtRegNo]='" &
Me.Registration_Number & "'")
 
Steve it now works fine.

Thank you very much for all your help and patience, it really is
appreciated.

Thanks Bob
Steve Schapel said:
Bob,

Thanks, I got the file, and I have spotted the problem... The field
isn't called Registration_Number it's called txtRegNo so the code needs
to refer to the field by its correct name, i.e.

ExistingReg =
DLookup("[txtRegPolicyNo]","tblRegNumbers","[txtRegNo]='" &
Me.Registration_Number & "'")

--
Steve Schapel, Microsoft Access MVP

Steve I will resend it now 21.37 if you do not receive it would you please
e-mail me @ (e-mail address removed)
I will reply to the address and hope fully you will receive it.

Thanks Bob
Click to expand...
Click to expand...
 
Back
Top