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.