Search in a combo box - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search in a combo box

Robert Freeman

2 years ago

Hi everyone. I need a bit of guidance with this please.
I have a combo box on a form where a previous occupation can be selected from a list.
I am trying to do a search on the previous occupation.
I am trying to create a query that can be used in the search vba (as Richard's videos).
Unfortunately I can't work out the links correctly - all my efforts result in type mismatches or SQL errors.
I'd appreciate guidance on what links are needed to achieve the search result.

I hope I've explained this clearly but if not please let me know what other infor is needed to help out.
I've attached three images - Table, Query and vba of the search button.

Thanks.
Bob.

	Robert Freeman OP @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago

	Robert Freeman OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
If you are searching using a Combo Box, is the string value you are searching for in the second column (the first being the ID)? Also in your tables You have PreviousOccupation in both tables. I would store the ID in the Member Details table. There is no join to relate the 2 tables together.

Robert Freeman OP @Reply

2 years ago

Hi Kevin.
After trying to sort the Type Mismatch I kept getting on the Query I've tracked down why. I had stored the PreviiousOccupation as short text instead of a number that it should have been to reference the PreviousOccupation_ID in that table.
I've now corrected that and got the query working - see screenshot attached.
I still not figure out the vba search references though - can you give me a pointer?

	Robert Freeman OP @Reply 2 years ago

Richard Rost @Reply

2 years ago

I think the best solution here would be to store OccupationID and PreviousOccupationID in the same table instead of using a string for the Previous. In fact, what you could do is just make a history of all occupations:

ID, OccupationID, DateStarted, DateEnded

1, 9, 2024-01-01, NULL

1, 29, 2023-06-01, 2024-01,01

This for example would let you see all of the previous occupations of this person had you could figure out the current one easily and the previous one. A lot of the times restructuring the data properly will allow you to query/report on it better.

	Richard Rost @Reply 2 years ago
Someone asked a similar question recently. I've got something like this on the list for a TechHelp video. Stay tuned.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View