Combo Box parameter query (1 Viewer)

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
Is it possible to add a parameter query to a field that is based on a combo box? The combo box is connected to a table. The one I have set up isn't working.:(
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
tell us more about the form, the Bound Column fo the combo is it numeric? where is the query connected to subform?
 

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
I have the query looking at the table that stores the data from the form. The particular field (Parish) in the table that I want to add the parameter query to, is set up as a combo and it looks at a table called Parish Names. The bound column for the parish field in the table is 1 and column count is 1.

I did originally have the field set as text and then changed it to a combo box. Not sure if that has caused problems.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
simply add criteria to the query.
on design view of the query:

Field: Parish
Criteria: [Forms]![yourFormName]![yourComboName]
 

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
I have entered the criteria, but no luck. The query will only allow me to look at Tables or Queries, not forms. I have set up the query based on tblCadastralPlansRegister. This is the table where the data is stored once it has been entered into the form. Is there a way I can do a parameter query on the parish field from this table? When I have used [Which Parish would you like to search for?] it doesn't work.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
if you can upload a sample db id like to know why.
 

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
I would rather not upload the database and can't work out how to show screen shots.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 20:50
Joined
Jul 9, 2003
Messages
16,245
I would rather not upload the database...

No need to upload your database. Create a new database, copy your form and table it's based on into the new dB. Run it, it will probably complain about missing queries, tables, forms. Copy those over.

Once you've got it working, then delete all the data from the tables and replace it with dummy data.

Post to the forum.

Sent from my SM-G925F using Tapatalk
 

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
Thanks for the advice. I have a sample one ready to upload. How do I upload it? I can only see a button to insert an image.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
its not showing, create a .zip and upload the zip file.
 

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
Hope it works this time. :banghead:
 

Attachments

  • Survey Central - demo to show Access world forum parish search not working 2018.09.17.zip
    702.4 KB · Views: 36

isladogs

MVP / VIP
Local time
Today, 20:50
Joined
Jan 14, 2017
Messages
18,186
You left out the form so that part can't be tested anyway.

However the query gives no results if I enter a name such as Barolin or Gregory. The reason is that you are using table level lookups for the Parish fields. So although the data is displayed as a parish name, it is actually stored as a number - the PK field from tblParishNames.

So to get results for e.g. Barolin I have to type 1... which is confusing!

Recommend you scrap table level lookups, display the actual value in the table and call it ParishID. Now create a query with both tables joined by the ID field.
Your parameter query will then work
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:50
Joined
May 7, 2009
Messages
19,169
i change the parish field to numeric.
see sample form in the attached.
view the code of the combo afterupdate event.
 

Attachments

  • Survey Central - demo to show Access world forum parish search not working 2018.09.17.accdb.zip
    710.8 KB · Views: 39

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
i change the parish field to numeric.
see sample form in the attached.
view the code of the combo afterupdate event.

Thank you arnelgp! That is great! Thank you for your assistance and time :):)
 

DatabaseTash

Registered User.
Local time
Tomorrow, 07:50
Joined
Jul 23, 2018
Messages
149
You left out the form so that part can't be tested anyway.

However the query gives no results if I enter a name such as Barolin or Gregory. The reason is that you are using table level lookups for the Parish fields. So although the data is displayed as a parish name, it is actually stored as a number - the PK field from tblParishNames.

So to get results for e.g. Barolin I have to type 1... which is confusing!

Recommend you scrap table level lookups, display the actual value in the table and call it ParishID. Now create a query with both tables joined by the ID field.
Your parameter query will then work


Thanks for the reply Ridders. When you say actual value do you mean to manually enter the value each time rather than choosing from the combo or do you mean change it to a value list?
 

isladogs

MVP / VIP
Local time
Today, 20:50
Joined
Jan 14, 2017
Messages
18,186
Thanks for the reply Ridders. When you say actual value do you mean to manually enter the value each time rather than choosing from the combo or do you mean change it to a value list?

I mean that you should use the ParishID number field without the table level lookup (combo). Then link the 2 tables together in a query to get the parish name
 

Users who are viewing this thread

Top Bottom