'Lookup' fields from another table in a form

Hi,

Some brief background - I’m relatively new to base and electronic databases in general. I watch a lot of live music and want to create a database to be able to record, query and display information about gigs I’ve seen and learn a new skill at the same time. I’ve used a series of Excel spreadsheets for a few years. I’ve done some reading and watched some of the ‘Frugal Computer Guy’ tutorials on you tube, and have converted my spreadsheets to a set of tables in (I think) normal form and have created a basic entry form with sub-form which works in terms of updating tables.

The problem I have is that to be able to enter data in the form, I first need to have manually looked up the correct ‘Art_Ref’ and ‘Ven_Ref’ from ‘tbl_Artists’ and ‘tbl_Venues’, and similarly in the subform all the relevant ‘Son_Ref’ from ‘tbl_Songs’.

Taking ‘Art_Ref’ as an example, I want the form to have a drop down search that allows a search/display on ‘Artist’ but enters ‘Art_Ref’ in ‘tbl_Gigs’ when I save the record.
(e.g. drop down search finds ‘Echo & The Bunnymen’ from ‘tbl_Artists’, ‘Artist’ but enters ‘A00654’ as ‘Art_Ref’ in ‘tbl_Gigs’).

You can assume for now that the ‘Artist’, ‘Venue’ and ‘Title’ and their associated entries are already included the the various tables.

I think this must be possible, and having tried a few things I’ve got close but not quite there (e.g. dropdown field with ‘Artist’, or a concatenation of ‘Artist’ and ‘Art_Ref’, but not writing ‘Art_Ref’ into ‘tbl_Gigs’.

I can’t post more that one JPEG as a new user, so can’t post a screenshot of the form.

Any help very gratefully received, thank you.

Screenshot of form/subform is here:

A form control writes some value into its form’s current record. A form control does not filter.
A subform shows records that are related to its parent form’s current record. The content of a subform changes as you navigate the parent form.
Possible solutions:

  1. The “form navigation” toolbar has a button to switch on a special mode of a form, the “form based filter”. In this mode an additional toolbar pops up, you can enter criteria and then finish this special mode and get a filtered form. The filter can be switched on/off or deleted on the navigation toolbar.
  2. The “Auto filter” button on the navigation form is handy when you spot your filter criterion on screen. Just focus the value and click the auto filter button.
  3. Then there is a dialog based filter analog to Calc’s “standard filter”. I don’t know why it is hidden, however you can access it like this: Hit the last button on the form navigation toolbar. This opens an additional grid view on the current form or subform with its own toolbar providing a “Standard Filter” button.
  1. And finally there is a “power filter” technique which makes use of the fact that a subform shows filtered data based on data stored in a parent form. The idea is to create a separate table (a single row actually) where you store criteria in order to filter your actual data in a subform.
    [Example #1] Filter/Search with Forms (leveraging SubForms)

P.S. one additional feature related to the form based filter: A text box has property “Filter proposal”. If set, it turns the text box into a listbox while in form filter mode. This makes sense when your strings are category names without any relation to another category table.

Thanks - I’ll work through this and see if I can get what I want.