Unwanted Enter Parameter Value Box (1 Viewer)

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
Hi all,
Question re an order input database (I set up years ago) that I'm having to make more user friendly for others.
I have a form that allows selection of a customer and a delivery run with a subform to select products and enter amounts etc.
To make it more user friendly I'm trying to use an unbound combo box with a row source that points to a table with product types (could do it on the product type field in the product table itself but just get the ID rather than the product type name.
In the subform all seems to work fine but when accessed through the main form I get an Enter parameter value? box.
Been chasing my tail trying to figure out how to make it work but could do with some help if anyone has any ideas...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:10
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

If you're using a Form reference, you will have to update it to include the subform. For example:

Forms!MainForm.SubForm.Form!ComboBox
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
Can't seem to find anywhere it needs updated... could it be a problem with the underlying relationships - they are a bit tortuous :-/
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
Whoops!


I've used SELECT DISTINCT in the Row Source of the unbound combo box with inner joins to related tables - would that maybe be the problem? Sorry been looking at it so long I've ceased to think clearly
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:10
Joined
Feb 28, 2001
Messages
26,999
Pardon me, but... .RowSource of an unbound combo box means there IS no row source. All combos and lists are bound separately from the form's binding anyway. And for the record, "SELECT DISTINCT" is perfectly legal in a row source for a combo box. And in fact, not that uncommon.

Rule number one for "Enter Parameter" boxes that you don't want/didn't expect include that you look at the spelling of what the box is asking for, because that name is what you told it - and you told it wrong.

It could be that a structure changed, it could be that a table changed, could be a lot of things. But that symptom almost ALWAYS means "I cannot find this thing you named even though you said I would need it." So start from the thing that is asking for the parameter and see whether you spelled something wrong.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:10
Joined
Oct 29, 2018
Messages
21,358
Whoops!


I've used SELECT DISTINCT in the Row Source of the unbound combo box with inner joins to related tables - would that maybe be the problem? Sorry been looking at it so long I've ceased to think clearly
Can you post the actual SQL statement?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 19, 2002
Messages
42,971
but just get the ID rather than the product type name
If you are talking about what shows in the combo, you can control that by using the Column widths property. Usually, we set the ID width to 0 so that the text field will show.

You might, in this case, need to join the primary table of the RowSource to the lookup table to select the text value. Then you would get the ID from the primary table and the text from the lookup table and your column widths value would be ----
0;1
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
Have changed it again (ad nauseam :-/) still doesn't make bacon ARRRGH!

Initially I wanted three dropdowns 1. Type 2. Sub Type and 3. Product - couldn't get it to work.
So have tried various ways of concatenating type and sub type the last of which is below.
I have a feeling this should be straightforward but I'm missing something...
Some screen shots below if anyone can help...

Relationships around it:
Relationships.JPG


Type Dropdown Row Source:
SELECT DISTINCT TypesAll.TypesAllID, [Type] & " " & [SubType] AS FullType FROM TSub INNER JOIN (T1 INNER JOIN (TypesAll INNER JOIN Product ON TypesAll.TypesAllID = Product.[ProductTypeID]) ON T1.TypeID = TypesAll.TypeID) ON TSub.ID = TypesAll.SubID;
Type Dropdown in subform.JPG



Subform record source:
Subform record source.JPG

Order input form with Enter parameter value box - appears on selection from dropdown
Order input form with Enter parameter value box - appears on selection from dropdown.JPG
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
If you are talking about what shows in the combo, you can control that by using the Column widths property. Usually, we set the ID width to 0 so that the text field will show.

You might, in this case, need to join the primary table of the RowSource to the lookup table to select the text value. Then you would get the ID from the primary table and the text from the lookup table and your column widths value would be ----
0;1
Thanks Pat but did play with that - previous post gives more detail - I'm now able to see the things I want to select - just can't select them!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 19, 2002
Messages
42,971
1. You don't need the junction table. Add TypeID to the subtype table. Bind the control to the SubTypeID (naming PK's ID is just silly and obfuscates relationships)
2. Whenever you use non-conforming names, you MUST enclose them in square brackets. The solution to that is to use only conforming names. i.e. ONLY - letters (upper or lower), numers, and the underscore. NEVER - special characters or embedded spaces.
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
Dropdown works in subform and can be selected but can't fully test as the main form is needed to enter the order ID

Dropdown works in subform and can be selected but can't fully test as the main form is needed ...jpg
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
1. You don't need the junction table. Add TypeID to the subtype table. Bind the control to the SubTypeID (naming PK's ID is just silly and obfuscates relationships)
2. Whenever you use non-conforming names, you MUST enclose them in square brackets. The solution to that is to use only conforming names. i.e. ONLY - letters (upper or lower), numers, and the underscore. NEVER - special characters or embedded spaces.
Pretty sure that's where I started - but hit the same problem when it came to the form - agree my naming is crap! (will work on it)
Do you see anything that may be causing this precise problem - the rest of the database is functioning fine...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:10
Joined
Oct 29, 2018
Messages
21,358
Have changed it again (ad nauseam :-/) still doesn't make bacon ARRRGH!
Did you try changing it the way I showed you how? Very important, you'll have to use the name of the subform control and not the name of the form it contains. So...

Forms!NameOfMainForm.NameOfSubformContainer.Form!NameOfDropdownControl
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
Was reference like that in the products field I want to filter - I used the builder so miss spelling wasn't/isn't the issue: [Forms]![Order Details Subform Copy]![TypesAllID]
I'll have a re-try with Sub types linked to types linked to the products as suggested by Pat but fairly sure I've done that already and hit the exact same problem at the end...
The dropdown works fine in the sub form by intself ... it's only when I call it from the main form I get the value query on selection... the dropdown is not referenced in the main form and is not recorded anywhere - it's purely to filter the products.
Think my approach is wrong - just wish I knew where!
Sorry got to pack it in for now...
Many thanks for pointers :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:10
Joined
Oct 29, 2018
Messages
21,358
Was reference like that in the products field I want to filter - I used the builder so miss spelling wasn't/isn't the issue: [Forms]![Order Details Subform Copy]![TypesAllID]
I'll have a re-try with Sub types linked to types linked to the products as suggested by Pat but fairly sure I've done that already and hit the exact same problem at the end...
The dropdown works fine in the sub form by intself ... it's only when I call it from the main form I get the value query on selection... the dropdown is not referenced in the main form and is not recorded anywhere - it's purely to filter the products.
Think my approach is wrong - just wish I knew where!
Sorry got to pack it in for now...
Many thanks for pointers :)
Hi. Not sure the expression builder would go that deep, especially since I don't see the extra "Form" keyword in it, but good luck with the new approach.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:10
Joined
Oct 29, 2018
Messages
21,358
The dropdown works fine in the sub form by intself ... it's only when I call it from the main form I get the value query on selection...
This is exactly what I was trying to address with the above syntax.
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:10
Joined
Sep 21, 2011
Messages
14,044
Was reference like that in the products field I want to filter - I used the builder so miss spelling wasn't/isn't the issue: [Forms]![Order Details Subform Copy]![TypesAllID]
I'll have a re-try with Sub types linked to types linked to the products as suggested by Pat but fairly sure I've done that already and hit the exact same problem at the end...
The dropdown works fine in the sub form by intself ... it's only when I call it from the main form I get the value query on selection... the dropdown is not referenced in the main form and is not recorded anywhere - it's purely to filter the products.
Think my approach is wrong - just wish I knew where!
Sorry got to pack it in for now...
Many thanks for pointers :)
Again, that is incorrect. TypesAllID is a property of the Form of the SubForm control [Order Details Subform Copy] ???
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 19, 2002
Messages
42,971
Delete the combo, save the form, compact, add the combo back
 

Linda1503

Member
Local time
Today, 17:10
Joined
Sep 3, 2021
Messages
77
This is exactly what I was trying to address with the above syntax.
You were right :))) - I was just too fraught to see - Sorry & thank You!
But... 1. When I try to create a new record in the subform the unbound combo box selection updates the previous record - is there a setting/macro that can make selection by each record?
Assuming not, I bound the combo box to the ProductTypeID (in the product table) resulting in the problem below - I have to assume there's something wrong with the underlying relationships but can't see how to fix it.
If I bind the combo box to the TypesAllID the dropdown works but I can't select them???
I know I'm probably being dense but if anyone can help me fix this I'd be eternally grateful - I usually find a way (not very efficiently) but this time I think I'm stymied
Just the bookkeeper trying to keep a small business functioning - so everything's always on the hoof :-/

With combo box bound:
New problem - when I try to create a new record in the subform, on selecting.JPG

relationships.JPG
 

Users who are viewing this thread

Top Bottom