I'm currently writing a booking system for the theatre company that I work for.

The image at the bottom of this post may explain it better, so you may want to  look at that first!, but my problem is:

The initial form ('New Gig') has 2 fields, 'GigID' (autonumber) and 'Event Name'

As soon as this form is submitted, it automatically creates a record in the  'Tech' report, autofilling the 'GigID' and 'Event Name' fields (there are actually lots more reports, but just to keep it simple to explain)

The problem I'm having, is that, although they've got the same field names and info, there's no 'relationship' between them.

I've tried putting a 'Event Name' lookup field on the 'Tech' form, and if I select the event from the lookup, and submit, the relationship is all good.

However, there are lots of forms (tech, invoicing, show reports, logistics etc.) and I'm trying to make it simple for our Bookings Manager  to use, so I don't really want her to have to use the lookup field for every form (even though it would only be once, just to establish the relationship.)

I've tried getting the 'Event Name' field (text) in the 'New Gig' form to autocomplete into the 'Event Name' lookup field in all the other forms (thus creating the relationship), but with no luck. I've tried it with the 'GigID' (autonumber) as well - still no joy.

Is there a way to automatically select an entry in a lookup field, without having to actually click the lookup and select the record that you want to create the relationship with?

The picture below may explain it better

Many thanks

Rich