Automatic fill ?

[tbaxter]

Okay, here is what I want to do:

I have form that requires input of a ship name and number. I'd like the number field to fill in automatically based on the ship name that the user inputs into the ship name field. To that end, I developed a table that contains each ship and its associated number. How do I get the form (and its associated table) to fill the number in automatically when a ship name is entered into the form?

I also have other items that I need similar automatic fill-ins. For example, we're asking for component serial numbers to be added for 3 components. I'd like the form to return the information for the lot number for the components and then store them in my main table.

Here are my tables:

MAIN (key field is EventID, also stores date, ship name & number, components A, B, C S/N and Lot No's, etc.)

Ship list (contains ship names and numbers -- key field is ship number)

CompA list (contains Component A S/N, Lot No., etc.)

CompB list (contains Component A S/N, Lot No., etc.)

CompC list (contains Component C S/N, Lot No., etc.)

I know it can be done but I'm not familiar enough with Access to do it. I've had a couple 1-day courses but they've only skimmed the surface.

Thanks for your help!

 

[Jack Cowley]

Question 1 - You can create a Combo box to hold your ship name and number (this way the user can not misspell the ship name). Let the number be the first (hidden if you like) column in the Combo and the ship name the second column. In the After Update event of the combo box create an Event Procedure and put in code similar to this:

Me![ShipIDNumber] = Me![ComboBoxName].Column(0)

When the user selects the ship name the ship number will be filled in.

I am not completely clear on your second request but maybe what I have described above will be of some assistance.

HTH,
Jack

[This message has been edited by Jack Cowley (edited 08-12-2000).]

 

[tbaxter]

Thanks you, Jack! I'll give that a try this morning.

To give more info for my second question: I guess I'll need to run an event that will, when a component serial number is entered, go to the corresponding table and look up the lot number and other info and return it to the form and to the table associated with the form. For example, the user enters a serial number for Component A (CompA) as 12345. Access grabs that number, goes to the CompA table and looks up the Lot Number for that particular component and returns it to the form and the table that the form updates. At least, that's what I need it to do; I just don't know enough to make it do that.

I don't know if that explains it any better but I hope so!

 

[tbaxter]

Jack,

I tried your suggestion; it didn't work. I got the following error when I tried to enter a new event:

The expression After Update you entered as the event property setting produced the following error: The object doesn't contain the Automation object "Me."

*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.

Also, how do I hide the first column in the combo box?

Thanks again!
Tina

 

[tbaxter]

Okay, I got the first column in the combo box hidden and I built the combo box from a table rather than inputting the values myself (which is how I did it the first time). Interestingly enough, I'm getting a different error now when I tab past the ShipName entry on the form.

Here's my event procedure in After Update of the combo box:

Me![ShipNo]=Me![ShipName].Column(0)

Here's the error message:

"Microsoft Access can't find the macro "Me![ShipNo]=Me!ShipName.Column(0)"

The macro (or its macro group) doesn't exist, or the macro is new but hasn't been saved.
Note that when you enter the macrogroupname.macroname syntax in an argument, you must specify the name the macro's macro group was last saved under."

Thanks,
Tina