How do I search 2 tables

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

dbl

Hi I am using the following to look up data in a table, how do I code it to
look in a second table (tblDownLoads) if the if formation required is not in
the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded from 2
different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
 
Bob

I can't be sure from your description, but it sounds like you are using two
tables because you have two sources of data. This is how you'd do it ... if
you were limited to using spreadsheets!

If the data is roughly the same (same data elements), there are plenty of
good reasons to build a normalized data structure in your Access tables that
lets you combine like data from separate sources. To point, you only have
to query one table!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
How about this:

Dim tbl As String
tbl = "tblRegNumbers"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
If IsNull(ExistingReg) Then
tbl = "tblDownLoads"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
End If
ExistingVType = DLookup("[txtVehicleType]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]", tbl, "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")

Carl Rapson
 
Carl that gives me the Error 2001 You cancelled the previous operation in
procedure Registration_Number after update.

If you put the old code back in after the end if it works on the first table
but obviously not on the second without errors

Bob
Carl Rapson said:
How about this:

Dim tbl As String
tbl = "tblRegNumbers"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
If IsNull(ExistingReg) Then
tbl = "tblDownLoads"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
End If
ExistingVType = DLookup("[txtVehicleType]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]", tbl, "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")

Carl Rapson

dbl said:
Hi I am using the following to look up data in a table, how do I code it
to look in a second table (tblDownLoads) if the if formation required is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded from
2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
 
Jeff there is no matching data in either table nor is there any duplicated
records the reason for splitting the tables is because the information will
be downloaded from different sources which will over write all the data in
that table. I hope that explains better what I am trying to do.

Bob
Jeff Boyce said:
Bob

I can't be sure from your description, but it sounds like you are using
two tables because you have two sources of data. This is how you'd do it
... if you were limited to using spreadsheets!

If the data is roughly the same (same data elements), there are plenty of
good reasons to build a normalized data structure in your Access tables
that lets you combine like data from separate sources. To point, you only
have to query one table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

dbl said:
Hi I am using the following to look up data in a table, how do I code it
to look in a second table (tblDownLoads) if the if formation required is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded from
2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
 
Damn, but you have some bloody ugly code!

Why not try this:

Dim recSet As ADODB.Recordset

Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic
recSet.ActiveConnection = CurrentProject.Connection

recSet.Open "select * from MyTable where SomeField = " & SomeValueIWant
If recSet.RecordCount < 1 Then
recSet.Close
recSet.Open "select * from MyTable2 where SomeField2 = " & SomeValueIWant2
If recSet.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
' I found my record in the second table - do something with it
End If
Else
' I found my record in the first table - do something with it
End If

recSet.Close
Set RecSet = Nothing




dbl said:
Carl that gives me the Error 2001 You cancelled the previous operation in
procedure Registration_Number after update.

If you put the old code back in after the end if it works on the first table
but obviously not on the second without errors

Bob
Carl Rapson said:
How about this:

Dim tbl As String
tbl = "tblRegNumbers"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
If IsNull(ExistingReg) Then
tbl = "tblDownLoads"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
End If
ExistingVType = DLookup("[txtVehicleType]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]", tbl, "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")

Carl Rapson

dbl said:
Hi I am using the following to look up data in a table, how do I code it
to look in a second table (tblDownLoads) if the if formation required is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded from
2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
I'm still not getting it.

I wasn't suggesting you had duplicate data, but that both sources had, say,
LastName, FirstName, DOB (similar fields).

I don't recall a mention in the original post about "overwriting" data in
the table. Are you saying that the incoming data is totally new, and your
Access table is just a temporary storage place? Or is the data just needing
to be updated?

Regards

Jeff Boyce
Microsoft Office/Access MVP

dbl said:
Jeff there is no matching data in either table nor is there any duplicated
records the reason for splitting the tables is because the information
will be downloaded from different sources which will over write all the
data in that table. I hope that explains better what I am trying to do.

Bob
Jeff Boyce said:
Bob

I can't be sure from your description, but it sounds like you are using
two tables because you have two sources of data. This is how you'd do it
... if you were limited to using spreadsheets!

If the data is roughly the same (same data elements), there are plenty of
good reasons to build a normalized data structure in your Access tables
that lets you combine like data from separate sources. To point, you
only have to query one table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

dbl said:
Hi I am using the following to look up data in a table, how do I code it
to look in a second table (tblDownLoads) if the if formation required is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded
from 2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
Sorry yes the incoming data is just data needing to be updated adding some
new data and removing some old data some of the data will remain the same,
we found the quickest and most accurate way was to delete all then install
the update. The table is only used as a storage place.


Jeff Boyce said:
I'm still not getting it.

I wasn't suggesting you had duplicate data, but that both sources had,
say, LastName, FirstName, DOB (similar fields).

I don't recall a mention in the original post about "overwriting" data in
the table. Are you saying that the incoming data is totally new, and your
Access table is just a temporary storage place? Or is the data just
needing to be updated?

Regards

Jeff Boyce
Microsoft Office/Access MVP

dbl said:
Jeff there is no matching data in either table nor is there any
duplicated records the reason for splitting the tables is because the
information will be downloaded from different sources which will over
write all the data in that table. I hope that explains better what I am
trying to do.

Bob
Jeff Boyce said:
Bob

I can't be sure from your description, but it sounds like you are using
two tables because you have two sources of data. This is how you'd do
it ... if you were limited to using spreadsheets!

If the data is roughly the same (same data elements), there are plenty
of good reasons to build a normalized data structure in your Access
tables that lets you combine like data from separate sources. To point,
you only have to query one table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi I am using the following to look up data in a table, how do I code
it to look in a second table (tblDownLoads) if the if formation
required is not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded
from 2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate =
DLookup("[txtStartDate]", "tblRegNumbers", "[txtRegNo]='" &
Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
Dennis this comes up with the following error on the first line
Dim recSet As ADODB.Recordset
Compile Error User defined type not defined
The code I have entered now looks like this, I take it that it works as
follows:

recset.Open "select * from tblRegNumbers where txtRegNo = " &
Me.Registration_Number (this line of code looks in the table tblRegNumbers
for the Registration Number in the field txtRegNo
if it doesn't find in it looks in the table tblDownLoads is this correct so
far?)

If it finds a match am I right in then listing the data I need from the
table as below

Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If

If that isn't how it works I am going to need more help to understand what
the code is trying to do

Thanks Bob

Dim recset As ADODB.Recordset

Set recset = New ADODB.Recordset
recset.CursorLocation = adUseClient
recset.CursorType = adOpenForwardOnly
recset.LockType = adLockOptimistic
recset.ActiveConnection = CurrentProject.Connection

recset.Open "select * from tblRegNumbers where txtRegNo = " &
Me.Registration_Number


If recset.RecordCount < 1 Then
recset.Close
recset.Open "select * from tblDownLoads where txtRegNo = " &
Me.Registration_Number

If recset.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If

recset.Close
Set recset = Nothing





Dennis said:
Damn, but you have some bloody ugly code!

Why not try this:

Dim recSet As ADODB.Recordset

Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic
recSet.ActiveConnection = CurrentProject.Connection

recSet.Open "select * from MyTable where SomeField = " & SomeValueIWant
If recSet.RecordCount < 1 Then
recSet.Close
recSet.Open "select * from MyTable2 where SomeField2 = " &
SomeValueIWant2
If recSet.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
' I found my record in the second table - do something with it
End If
Else
' I found my record in the first table - do something with it
End If

recSet.Close
Set RecSet = Nothing




dbl said:
Carl that gives me the Error 2001 You cancelled the previous operation in
procedure Registration_Number after update.

If you put the old code back in after the end if it works on the first
table
but obviously not on the second without errors

Bob
Carl Rapson said:
How about this:

Dim tbl As String
tbl = "tblRegNumbers"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
If IsNull(ExistingReg) Then
tbl = "tblDownLoads"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
End If
ExistingVType = DLookup("[txtVehicleType]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]", tbl, "[txtRegNo]='"
&
Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]", tbl, "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")

Carl Rapson

Hi I am using the following to look up data in a table, how do I code
it
to look in a second table (tblDownLoads) if the if formation required
is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded
from
2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]",
"tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel =
DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate =
DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
Check your references (under the Tools menu when you're in the VB Editor).

Do you have a reference set to the Microsoft ActiveX Data Object 2.n Library
(where n can be whichever version you want)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dbl said:
Dennis this comes up with the following error on the first line
Dim recSet As ADODB.Recordset
Compile Error User defined type not defined
The code I have entered now looks like this, I take it that it works as
follows:

recset.Open "select * from tblRegNumbers where txtRegNo = " &
Me.Registration_Number (this line of code looks in the table tblRegNumbers
for the Registration Number in the field txtRegNo
if it doesn't find in it looks in the table tblDownLoads is this correct
so far?)

If it finds a match am I right in then listing the data I need from the
table as below

Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If

If that isn't how it works I am going to need more help to understand what
the code is trying to do

Thanks Bob

Dim recset As ADODB.Recordset

Set recset = New ADODB.Recordset
recset.CursorLocation = adUseClient
recset.CursorType = adOpenForwardOnly
recset.LockType = adLockOptimistic
recset.ActiveConnection = CurrentProject.Connection

recset.Open "select * from tblRegNumbers where txtRegNo = " &
Me.Registration_Number


If recset.RecordCount < 1 Then
recset.Close
recset.Open "select * from tblDownLoads where txtRegNo = " &
Me.Registration_Number

If recset.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If

recset.Close
Set recset = Nothing





Dennis said:
Damn, but you have some bloody ugly code!

Why not try this:

Dim recSet As ADODB.Recordset

Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic
recSet.ActiveConnection = CurrentProject.Connection

recSet.Open "select * from MyTable where SomeField = " & SomeValueIWant
If recSet.RecordCount < 1 Then
recSet.Close
recSet.Open "select * from MyTable2 where SomeField2 = " &
SomeValueIWant2
If recSet.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
' I found my record in the second table - do something with it
End If
Else
' I found my record in the first table - do something with it
End If

recSet.Close
Set RecSet = Nothing




dbl said:
Carl that gives me the Error 2001 You cancelled the previous operation
in
procedure Registration_Number after update.

If you put the old code back in after the end if it works on the first
table
but obviously not on the second without errors

Bob
How about this:

Dim tbl As String
tbl = "tblRegNumbers"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
If IsNull(ExistingReg) Then
tbl = "tblDownLoads"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
End If
ExistingVType = DLookup("[txtVehicleType]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]", tbl,
"[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]", tbl, "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")

Carl Rapson

Hi I am using the following to look up data in a table, how do I code
it
to look in a second table (tblDownLoads) if the if formation required
is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded
from
2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]",
"tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel =
DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate =
DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
Yes we do, but if you try to un tick it the message comes up Cant remove.
Control or reference; in use

Regards Bob

Douglas J. Steele said:
Check your references (under the Tools menu when you're in the VB Editor).

Do you have a reference set to the Microsoft ActiveX Data Object 2.n
Library (where n can be whichever version you want)?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


dbl said:
Dennis this comes up with the following error on the first line
Dim recSet As ADODB.Recordset
Compile Error User defined type not defined
The code I have entered now looks like this, I take it that it works as
follows:

recset.Open "select * from tblRegNumbers where txtRegNo = " &
Me.Registration_Number (this line of code looks in the table
tblRegNumbers for the Registration Number in the field txtRegNo
if it doesn't find in it looks in the table tblDownLoads is this correct
so far?)

If it finds a match am I right in then listing the data I need from the
table as below

Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If

If that isn't how it works I am going to need more help to understand
what the code is trying to do

Thanks Bob

Dim recset As ADODB.Recordset

Set recset = New ADODB.Recordset
recset.CursorLocation = adUseClient
recset.CursorType = adOpenForwardOnly
recset.LockType = adLockOptimistic
recset.ActiveConnection = CurrentProject.Connection

recset.Open "select * from tblRegNumbers where txtRegNo = " &
Me.Registration_Number


If recset.RecordCount < 1 Then
recset.Close
recset.Open "select * from tblDownLoads where txtRegNo = " &
Me.Registration_Number

If recset.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If
Else
Me.Employer = txtRegEmployersName
Me.Policy_Number = txtRegPolicyNo
End If

recset.Close
Set recset = Nothing





Dennis said:
Damn, but you have some bloody ugly code!

Why not try this:

Dim recSet As ADODB.Recordset

Set recSet = New ADODB.Recordset
recSet.CursorLocation = adUseClient
recSet.CursorType = adOpenForwardOnly
recSet.LockType = adLockOptimistic
recSet.ActiveConnection = CurrentProject.Connection

recSet.Open "select * from MyTable where SomeField = " & SomeValueIWant
If recSet.RecordCount < 1 Then
recSet.Close
recSet.Open "select * from MyTable2 where SomeField2 = " &
SomeValueIWant2
If recSet.RecordCount < 1 Then
Debug.Print "I'm sorry, but you're hosed" ' Or other code here when
both fail
Else
' I found my record in the second table - do something with it
End If
Else
' I found my record in the first table - do something with it
End If

recSet.Close
Set RecSet = Nothing




:

Carl that gives me the Error 2001 You cancelled the previous operation
in
procedure Registration_Number after update.

If you put the old code back in after the end if it works on the first
table
but obviously not on the second without errors

Bob
How about this:

Dim tbl As String
tbl = "tblRegNumbers"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
If IsNull(ExistingReg) Then
tbl = "tblDownLoads"
ExistingReg = DLookup("[txtRegPolicyNo]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
End If
ExistingVType = DLookup("[txtVehicleType]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]", tbl,
"[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVModel = DLookup("[txtVehicleModel]", tbl, "[txtRegNo]='" &
Me.Registration_Number & "'")
ExistingVDate = DLookup("[txtStartDate]", tbl, "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")

Carl Rapson

Hi I am using the following to look up data in a table, how do I
code it
to look in a second table (tblDownLoads) if the if formation
required is
not in the first table (tblRegNumbers)?

The reason I want to have 2 tables is because the data is downloaded
from
2 different sources.

Dim ExistingReg As Variant
Dim ExistingVType As Variant
Dim ExistingEmployer As Variant
Dim ExistingVMake As Variant
Dim ExistingVModel As Variant
Dim ExistingVDate As Variant

ExistingReg = DLookup("[txtRegPolicyNo]", "tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVType = DLookup("[txtVehicleType]",
"tblRegNumbers",
"[txtRegNo]='" & Me.Registration_Number & "'")
ExistingEmployer = DLookup("[txtRegEmployersName]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVMake = DLookup("[txtVehicleMake]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVModel =
DLookup("[txtVehicleModel]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")
ExistingVDate =
DLookup("[txtStartDate]",
"tblRegNumbers", "[txtRegNo]='" & Me.Registration_Number & "'")


If Not IsNull(ExistingReg) Then
Me.Policy_Number = ExistingReg
Me.CarCommercial = ExistingVType
Me.Employer = ExistingEmployer
Me.Make___Model_of_Car = ExistingVMake & " " &
ExistingVModel
Me.Text364 = ExistingVDate
Me.OnCoverDate = Me.Text364


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