Importing other values from related tables

[NathanBurley]

Hi All,

I have two tables to track our engineer visits, one tracks the visit as a whole and the other tracks the individual instruments the engineer worked on during that visit. This way I can track visits to customer sites separately to the visits made to an individual instrument.

Right now, I create a visit and then add Visit Lines (containing the details of the instrument visited). These instruments, or 'Visit Lines', are being displayed in the Visits Table via a related items box. Visit lines are associated with Instruments in the Instrument Table via a serial number lookup. All this works great...

...however, I want to display both the serial number and the instrument description in the related items control in the Visits Table. Since the Visit Lines table only has the instrument ID lookup and not the instrument description I can't display it in the control.

I need to either:

a) Create an instrument description in the Visit Lines table as a lookup and have this automatically pull in the description based on the serial number the user selects.... which I can't figure out how to do - it's just an autocomplete.

b) Create an instrument description in Visit Lines and have a macro grab the corresponding description from the Instruments table based on the serial number input - but just for this record... which I also can't figure out.

Please help - I've sliced this lots of ways and can't figure this out.

Many thanks,

N

 

[Ranman256]

Seem you would need

tInstrument, (instrument table)
--------------------
[InstID] auto
[Name]
[SN]
[Description]
...etc


tVisit
-----------
[VisitID] auto
[InstID] long
[DateTime] as date -(general date to keep date and time)
[Results]
--etc

 

[NathanBurley]

Apologies Ranman, I am still pretty new to all this but is the answer you posted related to Access proper or to Access Web Apps? It's a web app solution I'm needing but I don't recognise where I would put that code into the web app system. I'm likely being dim though...

N

Seem you would need

tInstrument, (instrument table)
--------------------
[InstID] auto
[Name]
[SN]
[Description]
...etc


tVisit
-----------
[VisitID] auto
[InstID] long
[DateTime] as date -(general date to keep date and time)
[Results]
--etc

 

[NathanBurley]

mind you... it might have been helpful if I'd actually mentioned the fact I'm using a web app in the question... {face palm}. Sorry!

 

[Ranman256]

it is not code, just table layouts for you to record values.

 

[NathanBurley]

Err. OK, sorry I'm lost.

I already have three tables set out: Instruments, Visits, Visit Lines.

Instruments contains something like:
Serial Number
Location
etc

Visits Contains
Visit ID
Site Visited
Date or Visit
etc.

Visit Lines Contains
Visit ID [links to Visits table]
Instrument Serial Number [links to instruments table]
etc.

I can display the instrument description field from the instruments table in the Visit Lines record by doing a lookup vs the Serial Number but I can't automatically pull in the instrument description when the user completes the serial number lookup - they have to manually fill in the instrument description here. Which I don't want.

I need the Visit Lines table to contain this description as well so that when viewing the Visits List View - which displays the Visit Lines related to this visit - I get both the instrument serial number and the associated instrument description shown.

I'm not sure how the table layouts you give help me do that. But again, I'm pretty new at this... sorry.

it is not code, just table layouts for you to record values.

 

[NathanBurley]

Yeh, I'm being a bit dumb. The solution to this is to:

- Create a visit lines table which has a serial number lookup to the Instruments Table
- Create a separate field in that table with a instrument description lookup to the Instruments table
- Disable the Description field, preventing the user from accessing them.
- Allow the user to click the 'Add Visit Lines' button at the bottom of the related items control in the Visits Table - create a popup of the Visit Lines list view
- Allow the engineer to fill out the serial number field in the Visit Lines List view
- Replace the Save button on the action bar with a button which saves the records and then runs a macro performing the lookup between the Visit Lines and the Instruments table, matching the serial number to the instrument description.

This updates the entire table but it works just fine.