Making a Look-Up Form Work the Way I Want It To Work! (1 Viewer)

We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.

To do this, I have created two tables: tblDRIVER and tblLICENSE.

The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate

The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive

Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.

My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).

All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.

Thanks for the help.

I would use an AutoNumber to act as a unique ID for each driver.

What happens if a driver has 2 cars?

Col

I am using an AutoNumber for both tables and they are linked in a one-to-many relationship.

As far as "cars", it is of no concern how many cars a driver has. All we care about is that he/she has a valid drivers license and that there are no moving violations in the State's data base (which we check every time we up-date license information)

Rick

Col - No cars here, just drivers and licenses.

RHubbard - Is the relationship between drivers and licenses one to one? If si there are two ways to do this. First is to create a query that joins the two tables on DiverIndex. This gives you just one dataset so you can do it in one form. The second is to use the form/subform wizard to link the two sets of data. If you had adopted a naming convention that didn't use the prefixes on the field names, Access would spot the link field itself. As it is, you'll have to give it a nudge.

Hi Neil-
Actually, the relationship between tblDRIVER and tblLICENSE is one to many (since we track every license renewal or change as a NEW instance. E.g., we have many driver's who are intially licensed in a foreign state, but who, after establishing residency in this state, get one of ourt licenses. Also, some licenses up-grade form passenger to truck or heavy truck).

The link between the Form with driver info and related license info works fine. The problem seems to be this confounded look up form. I can't figure out why it will not retrive all the information on the underlying query.

Thanks,

Rick

So you need the form/subform wizard. You need two queries, one based on each table. Your subform will list all of the licences, subject to any restrictions you put in the underlying query.

My mistake - I interpreted "strLicNumber" as a cars licence plate

Col

Sorry, Rick, I'm not answering your question, am I?

What is your look up form? Does it have a combo box for the driver lookup? I need a bit more to go on. What data is it not retrieving?

Hi Neil-

I guess you are not answering my question because you must have intuitively known that I am approaching this whole probelm in a very bass-ackwards fashion....

What I REALLY need to be doing is to create a parameter query instead of this bizarre modus operandi!

Thanks for the help anyway.


Rick

If you included a combo box on your main form and used the value from the combo as the parameter for the underlying query, I'm sure this will give you what you want. You don't need a separate pop up form to capture the driver. Remember to requery the form in the After Update event of your combo.