Find out if record exists in 2nd table

[KevinPreston via AccessMonster.com]

Hopefully a quick question, i have 2 tables in my DB, one is vehicle details
(vehicles) the other is for booking jobs in (bookingcopy), the vehicles tbl
has registration number (regnumber) as the primary key, the bookingcopy tbl
has autonumber as primary key.

The booking table is used to book in any jobs required on a particular
vehicle, the vehicles table contains all the details about a particular
vehicle.

What i want to know is, when i enter a registration number into the regnumber
field in the bookingcopy table how do i check the vehicles table to see if
the vehicle details already exist.

I want to check so that if the vehicle details don't exist then i can open
the vheicle details form to enter the details for the vehicle then return to
the booking form to enter the job.

I hope that wasn't too longwinded and look forward to any help.

Kevin

 

[RonaldoOneNil]

In the After Update event of the textbox that enters the vehicle reg, put
this code

If IsNull(DLookup("[regnumber]","[vehicles]","[regnumber] = '" &
Me.regnumber & "'")) Then
Msgbox "Do something here because vehiocle does not exist"
Else
Msgbox "Do Something because Reg already exists"
End If

 

[Marshall Barton]

Hopefully a quick question, i have 2 tables in my DB, one is vehicle details
(vehicles) the other is for booking jobs in (bookingcopy), the vehicles tbl
has registration number (regnumber) as the primary key, the bookingcopy tbl
has autonumber as primary key.

The booking table is used to book in any jobs required on a particular
vehicle, the vehicles table contains all the details about a particular
vehicle.

What i want to know is, when i enter a registration number into the regnumber
field in the bookingcopy table how do i check the vehicles table to see if
the vehicle details already exist.

I want to check so that if the vehicle details don't exist then i can open
the vheicle details form to enter the details for the vehicle then return to
the booking form to enter the job.

You can use DLookup to see if a record exists. However, I
suggest that a better UI design would have users select the
vehicle before they try to add a booking. This is often
done by using a main form for the vehicle and a subform for
the bookings for the current main form vehicle.

 

[KevinPreston via AccessMonster.com]

In the After Update event of the textbox that enters the vehicle reg, put
this code

If IsNull(DLookup("[regnumber]","[vehicles]","[regnumber] = '" &
Me.regnumber & "'")) Then
Msgbox "Do something here because vehiocle does not exist"
Else
Msgbox "Do Something because Reg already exists"
End If

Hopefully a quick question, i have 2 tables in my DB, one is vehicle details
(vehicles) the other is for booking jobs in (bookingcopy), the vehicles tbl

[quoted text clipped - 16 lines]

Kevin

Thanks for quick response, much appreciated.

However, I have entered the code above and i am getting a runtime err (2001)
which says " you cancelled previous operation" and the code entered is then
couloured yellow, i am assuming that this means there is a syntax error.

I notice in your code that after "[regnumber] you have put = '" & and later
you have put & "'")) , how do you put the 3 apostrophes and 5 apostrophes? i
have tried everyway on my keyboard and can't do it.

Kevin

 

[KevinPreston via AccessMonster.com]

Hopefully a quick question, i have 2 tables in my DB, one is vehicle details
(vehicles) the other is for booking jobs in (bookingcopy), the vehicles tbl

[quoted text clipped - 12 lines]

the vheicle details form to enter the details for the vehicle then return to
the booking form to enter the job.

You can use DLookup to see if a record exists. However, I
suggest that a better UI design would have users select the
vehicle before they try to add a booking. This is often
done by using a main form for the vehicle and a subform for
the bookings for the current main form vehicle.

Thanks for swift reply.

I understand what you are saying and agree with you, but, i am not an expert
at Access, i am self taught, i Started this DB about 3 years ago to make my
job easier because we were doing everything on paper, which meant duplicating
the same thing 3 times, so i started it to print out the forms, to a remote
printer, then over time i got people asking can you change this can you make
it do that, so i added the vehicle details table and various other tables, to
cut it short the booking form as always been the main form where whoever is
booking in enters the date, reg, mileage, make, model and job description, it
then prints the forms, one on my printer and 3 on a remote printer.

The above is ok when i am doing it, because i know most of the vehicles about
500 commercial and about 600 cars, but if someone else does it then i want it
to fill the constant data automatically.

I am trying to rewrite it but struggling to find time, so at the moment i
tend to alter and play with y front end until i get it doing what i want.

Sorry about the lon explanatino.

Kevin

 

[Marshall Barton]

Hopefully a quick question, i have 2 tables in my DB, one is vehicle details
(vehicles) the other is for booking jobs in (bookingcopy), the vehicles tbl

[quoted text clipped - 12 lines]

the vheicle details form to enter the details for the vehicle then return to
the booking form to enter the job.

You can use DLookup to see if a record exists. However, I
suggest that a better UI design would have users select the
vehicle before they try to add a booking. This is often
done by using a main form for the vehicle and a subform for
the bookings for the current main form vehicle.

I understand what you are saying and agree with you, but, i am not an expert
at Access, i am self taught, i Started this DB about 3 years ago to make my
job easier because we were doing everything on paper, which meant duplicating
the same thing 3 times, so i started it to print out the forms, to a remote
printer, then over time i got people asking can you change this can you make
it do that, so i added the vehicle details table and various other tables, to
cut it short the booking form as always been the main form where whoever is
booking in enters the date, reg, mileage, make, model and job description, it
then prints the forms, one on my printer and 3 on a remote printer.

The above is ok when i am doing it, because i know most of the vehicles about
500 commercial and about 600 cars, but if someone else does it then i want it
to fill the constant data automatically.

THen use the DLookup approach Kevin said.

If you think there might be a syntax error in your code
(common when you first enter it), use the VBA editor's Debug
- Compile menu item to make sure the code compiles BEFORE
trying to run it.

Kevin did not use apostrophes everywhere The first set is
one apostrphe followed by a double quote. The second set is
" ' " a quoted apostrophe.

Note that you only use that if the criteria field is a Text
field. If it's a number type field then don't use the
apostrophes.

 

[KevinPreston via AccessMonster.com]

Hi Marshall

Thanks for your response and my apologies to Ronaldo for typing the code in
wrong, i should have copied & pasted, it would have saved me a lot of time,
but as i mentioned, i am learning as i go along.

I have corrected my typing and all seems to be working properly, but, now my
next cry for help!

If the vehicle exists in the vehicles tbl i want to automatically populate 3
fields on my booking in form, i have attempted to do it but appear to be
using wrong syntax.

I have eight fields on the booking in form, but only need to populate 3 of
them automatically.

jobnumber = text field, autonumber and primary key
regnumber = text field, data entered by user
mileage = text field, data entered by user
company = combo field, at moment company selected by user
make = combo field, at moment company selected by user
model = combo field, at moment company selected by user

What i want is to populate company,make & model from the vehicles table after
the user has entered the regnumber.

i have tried Me![company] = [vehicles].[company]

Can you point me in the right direction for referencing fields in another
table?

Hopefully the above explains what i am trying to do

Kevin

[quoted text clipped - 21 lines]

500 commercial and about 600 cars, but if someone else does it then i want it
to fill the constant data automatically.

THen use the DLookup approach Kevin said.

If you think there might be a syntax error in your code
(common when you first enter it), use the VBA editor's Debug
- Compile menu item to make sure the code compiles BEFORE
trying to run it.

Kevin did not use apostrophes everywhere The first set is
one apostrphe followed by a double quote. The second set is
" ' " a quoted apostrophe.

Note that you only use that if the criteria field is a Text
field. If it's a number type field then don't use the
apostrophes.

 

[Marshall Barton]

I have corrected my typing and all seems to be working properly, but, now my
next cry for help!

If the vehicle exists in the vehicles tbl i want to automatically populate 3
fields on my booking in form, i have attempted to do it but appear to be
using wrong syntax.

I have eight fields on the booking in form, but only need to populate 3 of
them automatically.

jobnumber = text field, autonumber and primary key
regnumber = text field, data entered by user
mileage = text field, data entered by user
company = combo field, at moment company selected by user
make = combo field, at moment company selected by user
model = combo field, at moment company selected by user

What i want is to populate company,make & model from the vehicles table after
the user has entered the regnumber.

i have tried Me![company] = [vehicles].[company]

Can you point me in the right direction for referencing fields in another
table?

Lots of ways to do that. A clumsy way would be to use
multiple Dlookup calls.

I suggest that you use a combo box for users to select or
type the regnumber. Then the combo box's row source query
can include the fields you want to display (with their
ColumnWidths corresponding values set to 0 so the don't
appear in the drop down list). Then the text boxes can
display the values using expressions like:
=cboRegNumber.Column(N)
where N is the 0 base column number.

A side benefit of using a combo box is that you can set its
LimitToList property to catch nonexistent reg numbers.

 

[KevinPreston via AccessMonster.com]

Marshall

Sorry for delay in responding, i have been busy and only just found time to
look at it again.

Your suggestion to use a combo box worked really well to a certain extent,
more to do with my booking form, i now have another 2 problems that i hope
you can help me with.

1st problem, i use your suggestion and now use a combo box for entering the
reg number, the problem is: we work on all types of vehicles, in my vehicle
details table any trailers have chassis number and a fleet number, ie A248401
& DG131

when booking in vehicles we refer to trailers by the fleet number, mainly
because the mechanics can't seem to read the chassis number and it is easier
for them to see the fleet number on the front of the trailer. Longwind
explanation but the question is

When i enter the fleet number in the regnumber combo box on the booking form,
how can i get the details for the trailer from the fleet number field in the
vehicles table?

2nd Problem. If i make cboRegnumber limit to list, how do i make the vehicles
form open so that the user can enter vehicle details then return to booking
and update the fields in that form.

Hopefully that makes sense, if not i will try to explain better..

Thanks
Kevin

I have corrected my typing and all seems to be working properly, but, now my
next cry for help!

[quoted text clipped - 20 lines]

Can you point me in the right direction for referencing fields in another
table?

Lots of ways to do that. A clumsy way would be to use
multiple Dlookup calls.

I suggest that you use a combo box for users to select or
type the regnumber. Then the combo box's row source query
can include the fields you want to display (with their
ColumnWidths corresponding values set to 0 so the don't
appear in the drop down list). Then the text boxes can
display the values using expressions like:
=cboRegNumber.Column(N)
where N is the 0 base column number.

A side benefit of using a combo box is that you can set its
LimitToList property to catch nonexistent reg numbers.

 

[Marshall Barton]

Sorry for delay in responding, i have been busy and only just found time to
look at it again.

Your suggestion to use a combo box worked really well to a certain extent,
more to do with my booking form, i now have another 2 problems that i hope
you can help me with.

1st problem, i use your suggestion and now use a combo box for entering the
reg number, the problem is: we work on all types of vehicles, in my vehicle
details table any trailers have chassis number and a fleet number, ie A248401
& DG131

when booking in vehicles we refer to trailers by the fleet number, mainly
because the mechanics can't seem to read the chassis number and it is easier
for them to see the fleet number on the front of the trailer. Longwind
explanation but the question is

When i enter the fleet number in the regnumber combo box on the booking form,
how can i get the details for the trailer from the fleet number field in the
vehicles table?

You're losing me somewhere between the reg, chassis and
fleet numbers. What does the user enter and what value is
the combo box supposed to have after the user selects
something? What is supposed to show in the combo box's drop
down list?

It sort of sounds like you want the combo box to do one
thing if the user selects/enters one kind of number and do
something else for another kind of number. If that's what
you want, it seems like you are describing two different
combo boxes to me.

2nd Problem. If i make cboRegnumber limit to list, how do i make the vehicles
form open so that the user can enter vehicle details then return to booking
and update the fields in that form.

Put some code in the combo box's NotInList event procedure
to open the other form (in Dialog mode).

 

[KevinPreston via AccessMonster.com]

Marshall

Thanks for reply and persevering with me, i will try and explain more clearly.


When a vehicle/trailer comes in to garage for work i book it in to the
database and print 4 different forms, 1 to my printer and 3 to the workshop
printer, previous to this discussion i was using the following booking form:

Job Number - autonumber field and primary key
Reg Number - text box field
date - date field
company - combo field. data from vehicles table
make - combo field. data from vehicles table
model - data from vehicles table.
job description - text field.

The user (normally me) enters the regnumber for vehicles or fleet number for
trailers, the date is filled in with current date automatically or i can
change it if i wish, i the select the company,make & model from the combo
boxes then lastly i enter the job description. All this is then saved in the
booking table.

The vehicles table has the following fields, along with others that are not
applicable to this discussion.

reg Number - text field, primary key
fleet number - text field
company - combo box - data from customers table
make - combo - data from make table
model - combo - data from model table

When i am booking in i mostly know if a vehicle is already in the database
because i am the only person that updates it so i can go straight to the
vehicles form and enter new details. What i want to do is automate the
booking process so that all the user has to enter is the regnumber/fleet
number and the job description and the other 3 fields are filled in
automatically.

Your suggestion worked perfectly if i was just entering a reg number as the
combo just puts up data from the vehicles.regnumber field, however the
problem is that if we have a trailer come in for work we always use the fleet
number to identify it, for two reasons, one it is easier to see as it is on
the headboard of the trailer and secondly the mechanics tend to be a bit
thick and cant be bothered to find the actual reg number off the chassis. So
what i want really is for the booking.regnumber combo to get its info from
two fields, vehicles.regnumber and vehicles.fleetnumber.

To get this working with your suggestion i had to use a query to get the
relevant info from the vehicles table and this worked fine apart from problem
i have mentioned, would this be better coded rather than using a query?

Hopefully you now understand what i want it to do, to me it looks as though
it should be easy to do what i want but i manage to sort one thing then find
another problem.

The reason i want to automate it is mainly for when i am not here, when
someone else does it i come back to work and have to over what they have done
and correct things like wrong reg, make or model etc.

Thanks for suggestion for 2nd problem, i will give that a go and see what i
can do.

Thanking you again for your assistance, i have learnt alot through this site
and the many helpful people like yourself.

Sorry for delay in responding, i have been busy and only just found time to
look at it again.

[quoted text clipped - 16 lines]

how can i get the details for the trailer from the fleet number field in the
vehicles table?

You're losing me somewhere between the reg, chassis and
fleet numbers. What does the user enter and what value is
the combo box supposed to have after the user selects
something? What is supposed to show in the combo box's drop
down list?

It sort of sounds like you want the combo box to do one
thing if the user selects/enters one kind of number and do
something else for another kind of number. If that's what
you want, it seems like you are describing two different
combo boxes to me.

2nd Problem. If i make cboRegnumber limit to list, how do i make the vehicles
form open so that the user can enter vehicle details then return to booking
and update the fields in that form.

Put some code in the combo box's NotInList event procedure
to open the other form (in Dialog mode).

 

[Marshall Barton]

When a vehicle/trailer comes in to garage for work i book it in to the
database and print 4 different forms, 1 to my printer and 3 to the workshop
printer, previous to this discussion i was using the following booking form:

Job Number - autonumber field and primary key
Reg Number - text box field
date - date field
company - combo field. data from vehicles table
make - combo field. data from vehicles table
model - data from vehicles table.
job description - text field.

The user (normally me) enters the regnumber for vehicles or fleet number for
trailers, the date is filled in with current date automatically or i can
change it if i wish, i the select the company,make & model from the combo
boxes then lastly i enter the job description. All this is then saved in the
booking table.

The vehicles table has the following fields, along with others that are not
applicable to this discussion.

reg Number - text field, primary key
fleet number - text field
company - combo box - data from customers table
make - combo - data from make table
model - combo - data from model table

When i am booking in i mostly know if a vehicle is already in the database
because i am the only person that updates it so i can go straight to the
vehicles form and enter new details. What i want to do is automate the
booking process so that all the user has to enter is the regnumber/fleet
number and the job description and the other 3 fields are filled in
automatically.

Your suggestion worked perfectly if i was just entering a reg number as the
combo just puts up data from the vehicles.regnumber field, however the
problem is that if we have a trailer come in for work we always use the fleet
number to identify it, for two reasons, one it is easier to see as it is on
the headboard of the trailer and secondly the mechanics tend to be a bit
thick and cant be bothered to find the actual reg number off the chassis. So
what i want really is for the booking.regnumber combo to get its info from
two fields, vehicles.regnumber and vehicles.fleetnumber.

To get this working with your suggestion i had to use a query to get the
relevant info from the vehicles table and this worked fine apart from problem
i have mentioned, would this be better coded rather than using a query?

Hopefully you now understand what i want it to do, to me it looks as though
it should be easy to do what i want but i manage to sort one thing then find
another problem.

The reason i want to automate it is mainly for when i am not here, when
someone else does it i come back to work and have to over what they have done
and correct things like wrong reg, make or model etc.

How can you tell the difference between a RegNumber and a
FleetNumber just by looking at them? If the difference is
not obvious, then it would be easy to select a vehicle when
you wantd to select a fleet trailer.

In the vehicles table, is the FleetNumber field unique among
trailers and Null for vehicles? If that's a valid way for
the program to distuinguish which is which, then create this
kind of query:
Warnings - untested air code
- must use SQL view

SELECT V.RegNumber, V.RegNumber As Display,
"Vehicle" As NumType, C.CompanyName,
Makes.MakeName, Models.ModelName
FROM Vehicles As V
INNER JOIN Companies As C
ON V.Company = C.CompanyID
INNER JOIN Makes
ON V.Make = Makes.MakeID
INNER JOIN Models
ON V.Model = Models.ModelID
WHERE V.FleetNumber Is Null
UNION ALL
SELECT V.RegNumber, V.FleetNumber,
"Trailer", C.CompanyName,
Makes.MakeName, Models.ModelName
FROM Vehicles As V
INNER JOIN Companies As C
ON V.Company = C.CompanyID
INNER JOIN Makes
ON V.Make = Makes.MakeID
INNER JOIN Models
ON V.Model = Models.ModelID
WHERE V.FleetNumber Is Not Null
ORDER BY NumType DESC, Display

I doubt that I guessed the table and field names correctly
so be sure to carefully check everything.

Once you get that query producing the needed records, then
set the combo box's RowSource to the query. Set the
ColumnCount to 6, Bound column to 1 and ColumnWidths to
0;1;1;0;0;0

Then set the text boxes' expression to:
company =thecombobox.Column(3)
make =thecombobox.Column(4)
model =thecombobox.Column(5)
(forget the DLookup)

 

[KevinPreston via AccessMonster.com]

Marshall

Thanks again for your reply.

In our case the difference between regnumbers and fleetnumbers is mostly easy
to distinguish, vehicles & trailers both have regnumbers and both can have
fleet numbers, a regnumber is normally the standard regnumber ie: r252wjd or
the new PE05WJD a trailer regnumber is normally a letter and 6 numbers ie:
A265935 a vehicle fleetnumber is normally 1 to 3 numbers but it is normally
only large fleets that have them, a trailer more often than not has a fleet
number aswell as the regnumber, normally in the format of 1 or more letters
and 1 or more numbers ie: T7 or DGI123. We normally exlusively use the
regnumber for vehicles and fleet number for trailers, mainly as explained
before because the mechanics cant seem to get the reg numbers right for
trailers, normally the fleet number is on the side of the trailer in big
leetter/numbers. Sorry there are a lot of normally's in that explanation.

Unfortunately the fleetnumbers are not unique among trailers and null for
vehicles, the only ID that is unique is the regnumber (in the vehicles table).


I think i am going to have a re-think of how to set up this booking in form
as i have also come up with another small related problem, i need to be able
to change the company name aswell, the majority of the time the
vehicle/trailer is owned by the operator but there are quite a few that are
leased so there is an owner and an operator, with some jobs being charged to
the owner and some to the operator.

It sounded so easy when i started to alter the form but i keep finding
something new to sort out, although it is making me learn more about Access.

Your original suggestion would work perfectly if it were just one field for
regnumber/fleetnumber and owner/operator.

Thanks very much for your help upto now and i am sure i will be asking for
more in the not to distant future.

kevin

When a vehicle/trailer comes in to garage for work i book it in to the
database and print 4 different forms, 1 to my printer and 3 to the workshop

[quoted text clipped - 50 lines]

someone else does it i come back to work and have to over what they have done
and correct things like wrong reg, make or model etc.

How can you tell the difference between a RegNumber and a
FleetNumber just by looking at them? If the difference is
not obvious, then it would be easy to select a vehicle when
you wantd to select a fleet trailer.

In the vehicles table, is the FleetNumber field unique among
trailers and Null for vehicles? If that's a valid way for
the program to distuinguish which is which, then create this
kind of query:
Warnings - untested air code
- must use SQL view

SELECT V.RegNumber, V.RegNumber As Display,
"Vehicle" As NumType, C.CompanyName,
Makes.MakeName, Models.ModelName
FROM Vehicles As V
INNER JOIN Companies As C
ON V.Company = C.CompanyID
INNER JOIN Makes
ON V.Make = Makes.MakeID
INNER JOIN Models
ON V.Model = Models.ModelID
WHERE V.FleetNumber Is Null
UNION ALL
SELECT V.RegNumber, V.FleetNumber,
"Trailer", C.CompanyName,
Makes.MakeName, Models.ModelName
FROM Vehicles As V
INNER JOIN Companies As C
ON V.Company = C.CompanyID
INNER JOIN Makes
ON V.Make = Makes.MakeID
INNER JOIN Models
ON V.Model = Models.ModelID
WHERE V.FleetNumber Is Not Null
ORDER BY NumType DESC, Display

I doubt that I guessed the table and field names correctly
so be sure to carefully check everything.

Once you get that query producing the needed records, then
set the combo box's RowSource to the query. Set the
ColumnCount to 6, Bound column to 1 and ColumnWidths to
0;1;1;0;0;0

Then set the text boxes' expression to:
company =thecombobox.Column(3)
make =thecombobox.Column(4)
model =thecombobox.Column(5)
(forget the DLookup)

 

[Marshall Barton]

Some rethinking is probably a very good idea.

If the fleet number is not unique, how can you use it to
identify a trailer?

If you really want to use one combo box, you must have some
way to distinguish between vehicle and trailer records.
Maybe you need another field that contains either "Vehicle"
or "Trailer"??

I was going to suggest that it might be easier to use two
combo boxes, one for vehicles and one for trailers, but that
won't work either unless you can separate the two sets of
records. Your explanation about the number "USUALLY" being
a different number of digits is useless unless you can come
up with a set of rules that eliminate the "usually".
--
Marsh
MVP [MS Access]

In our case the difference between regnumbers and fleetnumbers is mostly easy
to distinguish, vehicles & trailers both have regnumbers and both can have
fleet numbers, a regnumber is normally the standard regnumber ie: r252wjd or
the new PE05WJD a trailer regnumber is normally a letter and 6 numbers ie:
A265935 a vehicle fleetnumber is normally 1 to 3 numbers but it is normally
only large fleets that have them, a trailer more often than not has a fleet
number aswell as the regnumber, normally in the format of 1 or more letters
and 1 or more numbers ie: T7 or DGI123. We normally exlusively use the
regnumber for vehicles and fleet number for trailers, mainly as explained
before because the mechanics cant seem to get the reg numbers right for
trailers, normally the fleet number is on the side of the trailer in big
leetter/numbers. Sorry there are a lot of normally's in that explanation.

Unfortunately the fleetnumbers are not unique among trailers and null for
vehicles, the only ID that is unique is the regnumber (in the vehicles table).


I think i am going to have a re-think of how to set up this booking in form
as i have also come up with another small related problem, i need to be able
to change the company name aswell, the majority of the time the
vehicle/trailer is owned by the operator but there are quite a few that are
leased so there is an owner and an operator, with some jobs being charged to
the owner and some to the operator.

It sounded so easy when i started to alter the form but i keep finding
something new to sort out, although it is making me learn more about Access.

Your original suggestion would work perfectly if it were just one field for
regnumber/fleetnumber and owner/operator.

Thanks very much for your help upto now and i am sure i will be asking for
more in the not to distant future.

kevin

When a vehicle/trailer comes in to garage for work i book it in to the
database and print 4 different forms, 1 to my printer and 3 to the workshop

[quoted text clipped - 50 lines]

someone else does it i come back to work and have to over what they have done
and correct things like wrong reg, make or model etc.

How can you tell the difference between a RegNumber and a
FleetNumber just by looking at them? If the difference is
not obvious, then it would be easy to select a vehicle when
you wantd to select a fleet trailer.

In the vehicles table, is the FleetNumber field unique among
trailers and Null for vehicles? If that's a valid way for
the program to distuinguish which is which, then create this
kind of query:
Warnings - untested air code
- must use SQL view

SELECT V.RegNumber, V.RegNumber As Display,
"Vehicle" As NumType, C.CompanyName,
Makes.MakeName, Models.ModelName
FROM Vehicles As V
INNER JOIN Companies As C
ON V.Company = C.CompanyID
INNER JOIN Makes
ON V.Make = Makes.MakeID
INNER JOIN Models
ON V.Model = Models.ModelID
WHERE V.FleetNumber Is Null
UNION ALL
SELECT V.RegNumber, V.FleetNumber,
"Trailer", C.CompanyName,
Makes.MakeName, Models.ModelName
FROM Vehicles As V
INNER JOIN Companies As C
ON V.Company = C.CompanyID
INNER JOIN Makes
ON V.Make = Makes.MakeID
INNER JOIN Models
ON V.Model = Models.ModelID
WHERE V.FleetNumber Is Not Null
ORDER BY NumType DESC, Display

I doubt that I guessed the table and field names correctly
so be sure to carefully check everything.

Once you get that query producing the needed records, then
set the combo box's RowSource to the query. Set the
ColumnCount to 6, Bound column to 1 and ColumnWidths to
0;1;1;0;0;0

Then set the text boxes' expression to:
company =thecombobox.Column(3)
make =thecombobox.Column(4)
model =thecombobox.Column(5)
(forget the DLookup)

 

[KevinPreston via AccessMonster.com]

Marshall

Apologies for delay in replying, been very busy.

Thank you very much for your input with this problem, although i haven't
sorted out what i wanted to do i have learnt quite alot about access, now
need to learn more.

The trailer fleet number issue is not too important and i will figure it out
eventually but unfortunately i haven't got the luxury of working on it until
fixed, i have to use it, the whole DB is much better than when i first
started 3 yrs ago.

When booking in a trailer it is fairly easy for me to do it because i do all
the booking, except when on holiday, then i have a great mess to sort when i
get back.

What i have been doing is just typing the fleet number into a text box on the
form, which then gets printed with everything else. I just wanted to automate
it because i am duplicating info that is already in the DB and it makes it
easier for other people to book in.

Sorry this is turning into a long winded post again, just to explain the
trailers, sorry if i am repeating stuff.

The vehicle/trailer records are all in one table, the primary key is the
registration number, which makes it simple for vehicles, trailers have a
registration number, which is what they are entered into the DB with, but
they also have a fleet number which is normally printed in large lettering on
the front or side of the trailer, this number is used because it is easier
for people to see it, the reg is on the side of the chassis towards the back
of the trailer. The fleet number can be of differing lengths depending on the
owners numbering scheme, some have T1 to mean trailer 1, another company will
have TF1 or ETL020 etc that's why i said 'usually'.

I will drop it for a bit and come back to it, i am in the process of
completely rewriting the DB but i will need to import the data as i have
about 1200 vehicles/trailers/cars and over 10'000 records in the booking
table that i need to keep.

So once again thanks for your assistance and no doubt i will be asking for it
again in the not too distant future.

kevin

Some rethinking is probably a very good idea.

If the fleet number is not unique, how can you use it to
identify a trailer?

If you really want to use one combo box, you must have some
way to distinguish between vehicle and trailer records.
Maybe you need another field that contains either "Vehicle"
or "Trailer"??

I was going to suggest that it might be easier to use two
combo boxes, one for vehicles and one for trailers, but that
won't work either unless you can separate the two sets of
records. Your explanation about the number "USUALLY" being
a different number of digits is useless unless you can come
up with a set of rules that eliminate the "usually".

In our case the difference between regnumbers and fleetnumbers is mostly easy
to distinguish, vehicles & trailers both have regnumbers and both can have

[quoted text clipped - 86 lines]

 

[Marshall Barton]

Thank you very much for your input with this problem, although i haven't
sorted out what i wanted to do i have learnt quite alot about access, now
need to learn more.

The trailer fleet number issue is not too important and i will figure it out
eventually but unfortunately i haven't got the luxury of working on it until
fixed, i have to use it, the whole DB is much better than when i first
started 3 yrs ago.

When booking in a trailer it is fairly easy for me to do it because i do all
the booking, except when on holiday, then i have a great mess to sort when i
get back.

What i have been doing is just typing the fleet number into a text box on the
form, which then gets printed with everything else. I just wanted to automate
it because i am duplicating info that is already in the DB and it makes it
easier for other people to book in.

Sorry this is turning into a long winded post again, just to explain the
trailers, sorry if i am repeating stuff.

The vehicle/trailer records are all in one table, the primary key is the
registration number, which makes it simple for vehicles, trailers have a
registration number, which is what they are entered into the DB with, but
they also have a fleet number which is normally printed in large lettering on
the front or side of the trailer, this number is used because it is easier
for people to see it, the reg is on the side of the chassis towards the back
of the trailer. The fleet number can be of differing lengths depending on the
owners numbering scheme, some have T1 to mean trailer 1, another company will
have TF1 or ETL020 etc that's why i said 'usually'.

I will drop it for a bit and come back to it, i am in the process of
completely rewriting the DB but i will need to import the data as i have
about 1200 vehicles/trailers/cars and over 10'000 records in the booking
table that i need to keep.

While you are rewriting the db, think about how to
distinguish between vehicles and trailers. It seems like
keeping them in the same table makes sense, but you need a
way to tell which is which. It might be just that vehicles
have a null value in the fleet number field or, if that's
not feasible, another field in the table that says this is a
vehicle record or this is a trailer record.

The more forethought you put into it up front can pay back
manyfold in the future so don't be in too big a hurry to
just rewrite stuff.