Combo box for searching on a form

[sandrelei]

Hello,

I have designed a simple database but am strugling with the most
important part of it.
I have a database of service calls on vehicles. I import a list of not
working vehicles onto a table. Then I have a form, where I need to
search for a particular vehicle, make notes on the service it has had.
When it is working again, I tick the box, so it is closed. So, as you
can imagine the same vehicles may stop working again, so I will have
some records with the same registration number.
What I am trying to do is create a combo box on a form and do a search
by a registration number. I would like it to search only for open
service calls. I cannot find the way to do that.
I would appreciate if anyone could help. Or maybe you know an easier
way to search for open service calls.
I know how to do queries and reports, but the my problem is, i need
not only to find the records but also to add some notes, therefore, i
thought i should do it on a form...
Please please please... I would appreciate any help.

Sandra

 

[Jeff Boyce]

Sandra

How do YOU know a vehicle has an "open service call"? Is the information
you use to determine that being stored in your Access database/table? If
not, how would Access be able to know?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi sandrelei,

What you want to do is design two forms: main form and sub-form. First,
design a Main form with no "Record Source". Then on that Main form, create
an "unbound combo box" for the registration number.

Second, design a sub-form within the Main form with "Record Source" from the
table that you have your records (one of the fields must be registration
number). Then link the registration number in the sub-form with the unbound
combo box on the Main form. In the query of Record Source of the sub-form,
set the field that you "tick" the box to "Yes".

Everytime, when you select a registration number from the combo box on the
Main form, the sub-form will display only the records for that registration
number. If no records available for that registration number, it will show
nothing. On the other hand, if you only want the unbound combo box to show
the registration # with the available records, set the field that you "tick"
the box to "Yes" (just like from above) in the query of "Row Source" of that
unbound combo box.

I hope that is what you are looking for.

 

[sandrelei]

Reply to Jeff Boyce

I have a table with the fields:
Date Vehicle Registration Make Model Status
Visited Notes Closed Date Closed
30/07/2007 1235 Audi A4 No GPS

Some fields are filled in initially. Then when engineer calls I go
onto a form, i want the combo box to search by the registration and
open the details of that vehicle. Then if the vehicle if fixed i wite
the Notes and tick the box Closed.

Lets say in the future the same vehicle has a fault again. So I add it
to the database again. Then if I do a search using a combo box, I want
the combo box to open the details of this new service call and ignore
the old one, which is closed. As you see the Registration number is
not a primary key, it can be repeated on the same table. but that is
the only item i can search by.

By the way, I am using columnar form, which shows only one record, not
a tabular which would show a whole table of records...

 

[Jeff Boyce]

So, you are saying that any row/record that has a value in both [Closed] (?a
Yes/No field?) and in [DateClosed] (?a date/time field?) is not open? First
things, why TWO? Wouldn't it be enough just to have a [DateClosed] field?
Any record with a value in that field would be closed, right?

So any record with data, but NO [Closed] or [DateClosed] is still open,
right? That sounds like something a query could pull out easily. And if
you have a form on which you can select a given vehicle (i.e., via a combo
box), you could use that same query and add a parameter for the vehicle that
only pulls up that vehicles "open" records.

I may be misinterpreting your description... it sounds like you are
reentering all the data about the vehicle for each "service". The example
data you gave also appears to support this interpretation.

Access is a relational database, not a spreadsheet. There's no need to
repeat the same data (e.g., "Audi", "A4", "No GPS", "1235") every time you
do a service. Instead, use one table that holds vehicle info ...
tblVehicle
VehicleID (a unique identifier ... ?VIN?)
Make
Model
Note
Registration
... other vehicle-specific data
and another that holds "service" info...
tblService
ServiceID
VehicleID
ServiceDate
ServiceNote
DateTimeClosed
... other service-specific info

This way, you only need to save the VehicleID in the Service record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[sandrelei]

So, you are saying that any row/record that has a value in both [Closed] (?a
Yes/No field?) and in [DateClosed] (?a date/time field?) is not open? First
things, why TWO? Wouldn't it be enough just to have a [DateClosed] field?
Any record with a value in that field would be closed, right?

So any record with data, but NO [Closed] or [DateClosed] is still open,
right? That sounds like something a query could pull out easily. And if
you have aformon which you can select a given vehicle (i.e., via acombobox), you could use that same query and add a parameter for the vehicle that
only pulls up that vehicles "open" records.

I may be misinterpreting your description... it sounds like you are
reentering all the data about the vehicle for each "service". The example
data you gave also appears to support this interpretation.

Access is a relational database, not a spreadsheet. There's no need to
repeat the same data (e.g., "Audi", "A4", "No GPS", "1235") every time you
do a service. Instead, use one table that holds vehicle info ...
tblVehicle
VehicleID (a unique identifier ... ?VIN?)
Make
Model
Note
Registration
... other vehicle-specific data
and another that holds "service" info...
tblService
ServiceID
VehicleID
ServiceDate
ServiceNote
DateTimeClosed
... other service-specific info

This way, you only need to save the VehicleID in the Service record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reply to Jeff Boyce

I have a table with the fields:
Date Vehicle Registration Make Model Status
Visited Notes Closed Date Closed
30/07/2007 1235 Audi A4 No GPS

Some fields are filled in initially. Then when engineer calls I go
onto aform, i want thecomboboxtosearchby the registration and
open the details of that vehicle. Then if the vehicle if fixed i wite
the Notes and tick theboxClosed.

Lets say in the future the same vehicle has a fault again. So I add it
to the database again. Then if I do asearchusing acombobox, I want
thecomboboxto open the details of this new service call and ignore
the old one, which is closed. As you see the Registration number is
not a primary key, it can be repeated on the same table. but that is
the only item i cansearchby.

By the way, I am using columnarform, which shows only one record, not
a tabular which would show a whole table of records...- Hide quoted text -

- Show quoted text -

Hi there,

Thanks a lot. You have interpreted it right. I know that it is
possible to do it how you suggested (i.e. have vehicle info and then
service record). The problem is, that we have an online system, where
the vehicles download journeys (tracking system), when they stop for
whatever reason, we export a list of non working vehicles and import
it straight into an access database...
Anyway, i will try with the query.
By the way, where do learn all these things? is there some good online
course that i could look into?

Regards,
Sandra

 

[Jeff Boyce]

In my case at least, it was acquired through painful trial and error, and
frequent trips to these newsgroups!

Regards

Jeff Boyce
Microsoft Office/Access MVP

So, you are saying that any row/record that has a value in both [Closed]
(?a
Yes/No field?) and in [DateClosed] (?a date/time field?) is not open?
First
things, why TWO? Wouldn't it be enough just to have a [DateClosed]
field?
Any record with a value in that field would be closed, right?

So any record with data, but NO [Closed] or [DateClosed] is still open,
right? That sounds like something a query could pull out easily. And if
you have aformon which you can select a given vehicle (i.e., via
acombobox), you could use that same query and add a parameter for the
vehicle that
only pulls up that vehicles "open" records.

I may be misinterpreting your description... it sounds like you are
reentering all the data about the vehicle for each "service". The
example
data you gave also appears to support this interpretation.

Access is a relational database, not a spreadsheet. There's no need to
repeat the same data (e.g., "Audi", "A4", "No GPS", "1235") every time
you
do a service. Instead, use one table that holds vehicle info ...
tblVehicle
VehicleID (a unique identifier ... ?VIN?)
Make
Model
Note
Registration
... other vehicle-specific data
and another that holds "service" info...
tblService
ServiceID
VehicleID
ServiceDate
ServiceNote
DateTimeClosed
... other service-specific info

This way, you only need to save the VehicleID in the Service record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Reply to Jeff Boyce

I have a table with the fields:
Date Vehicle Registration Make Model Status
Visited Notes Closed Date Closed
30/07/2007 1235 Audi A4 No GPS

Some fields are filled in initially. Then when engineer calls I go
onto aform, i want thecomboboxtosearchby the registration and
open the details of that vehicle. Then if the vehicle if fixed i wite
the Notes and tick theboxClosed.

Lets say in the future the same vehicle has a fault again. So I add it
to the database again. Then if I do asearchusing acombobox, I want
thecomboboxto open the details of this new service call and ignore
the old one, which is closed. As you see the Registration number is
not a primary key, it can be repeated on the same table. but that is
the only item i cansearchby.

By the way, I am using columnarform, which shows only one record, not
a tabular which would show a whole table of records...- Hide quoted
text -

- Show quoted text -

Hi there,

Thanks a lot. You have interpreted it right. I know that it is
possible to do it how you suggested (i.e. have vehicle info and then
service record). The problem is, that we have an online system, where
the vehicles download journeys (tracking system), when they stop for
whatever reason, we export a list of non working vehicles and import
it straight into an access database...
Anyway, i will try with the query.
By the way, where do learn all these things? is there some good online
course that i could look into?

Regards,
Sandra