Reference form controls in query

[Guest]

I have created an update query with 3 fields.
1. Markup – This is the field I intend to update. In the “Update To:†box
in the query, I have [Enter the new multiplier:], which will prompt users to
enter the new value when the query is run. This works as desired.
2. ProjectID – In the “Criteria:†box, I have [Forms]![Bid
Subform].[ProjectID]
3. BidNumber - In the “Criteria:†box, I have [Forms]![Bid
Subform].[BidNumber]

I worked great when the ProjectID and BidNumber criteria were entered into
the query design view, but I need users to be able to do this from the form
“Bid Subformâ€, so I tried to reference the controls in the subform so that
when you are working in a particular BidNumber, it would updated only the
records related to it. I intend to place a button on that subform that would
run the query. The only thing users should have to enter is the new value in
Markup when prompted.

The problem I have is that when I run the query, it still asks for the
parameter value of ProjectID & BidNumber in subsequent dialogue boxes. I
have also tried a ! between the subform reference and control reference.
This yielded the same result.

What might I have wrong with the criteria? Thanks for any feedback!

 

[Guest]

2. ProjectID – In the “Criteria:†box, I have [Forms]![Bid
Subform].[ProjectID]
Use [Forms]![YourMainFormName]![Bid Subform].[ProjectID]

3. BidNumber - In the “Criteria:†box, I have [Forms]![Bid Subform].[BidNumber]

Use [Forms]![YourMainFormName]![Bid Subform].[BidNumber]

I have created an update query with 3 fields.
1. Markup – This is the field I intend to update. In the “Update To:†box
in the query, I have [Enter the new multiplier:], which will prompt users to
enter the new value when the query is run. This works as desired.
2. ProjectID – In the “Criteria:†box, I have [Forms]![Bid
Subform].[ProjectID]
3. BidNumber - In the “Criteria:†box, I have [Forms]![Bid
Subform].[BidNumber]

I worked great when the ProjectID and BidNumber criteria were entered into
the query design view, but I need users to be able to do this from the form
“Bid Subformâ€, so I tried to reference the controls in the subform so that
when you are working in a particular BidNumber, it would updated only the
records related to it. I intend to place a button on that subform that would
run the query. The only thing users should have to enter is the new value in
Markup when prompted.

The problem I have is that when I run the query, it still asks for the
parameter value of ProjectID & BidNumber in subsequent dialogue boxes. I
have also tried a ! between the subform reference and control reference.
This yielded the same result.

What might I have wrong with the criteria? Thanks for any feedback!

 

[Guest]

I appreciate your response! I tried what you suggested but got the same
result, but I did resort to a little trial and error and got closer to a
solution. In the ProjectID criteria, I got the following to work:
[Forms]![BidEntryFormEXP].[Combo68]
BidEntryFormEXP is the main form name, and Combo68 is the name of the
control where ProjectID is chosen. It now recognizes the ProjectID, but I
still get prompted for the BidNumber. Is there any other reason that would
occur? The control name is "BidNumber" as is the field itself. Could that
be confusing the expression? Should I create another control with a
different name? Again, I sure appreciate the help with this!

 

[Guest]

Yes, you should not have two controls with the same name on the same form.
What kind of control is the "BidNumber"? Is it a Combo also.

The name of the form is not the same in your first post - why?

 

[Guest]

I did not include the main form name in my first post, but then started using
that reference based on your reply. As it turned out, referencing the
control on the main form rather than the subform is what led to that
criteria working properly.

I may have confused you a bit. There are not 2 controls on the same form
with the same name. BidNumberis a text box, but both the Control Source and
Name in the properties was listed as BidNumber, so that's what I thought may
have been causing a problem. I have since tried changing the name to
"txtBidNumber", but that did not resolve this either. Any other suggestions?

Other possible ideas I am throwing out there:
Should I try a concatenated field in the query with both ProjectID &
BidNumber?
Could this have something to do with the Linked Master & Child fields?
(Don't know why it would because the form/subform perform properly.)

Thanks again!

Do you think this could have something to do with the

 

[Guest]

Ok, the one that is not working now is what kind of control where you
indicate the criteria for the query?

Also post your SQL statement of the query.

 

[Guest]

What kind of control? - - - It is a textbox.

SQL of the query (Please note I changed the criteria to "Enter the bid
number:" in the BidNumber field. See previous posts for how it was listed
previously.) - - -

UPDATE Project INNER JOIN ((Bid INNER JOIN Item ON (Bid.ProjectID =
Item.ProjectID) AND (Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName =
Item.ProjectName)) INNER JOIN ItemDetail ON (Item.ItemNumber =
ItemDetail.ItemNumber) AND (Item.RoomNumber = ItemDetail.RoomNumber) AND
(Item.BidNumber = ItemDetail.BidNumber) AND (Item.ProjectID =
ItemDetail.ProjectID) AND (Item.ProjectName = ItemDetail.ProjectName)) ON
(Project.ProjectName = Bid.ProjectName) AND (Project.ProjectID =
Bid.ProjectID) SET ItemDetail.Markup = [Enter the new multiplier:]
WHERE (((Item.ProjectID)=[Forms]![BidEntryFormEXP].[Combo68]) AND
((Item.BidNumber)=[Enter the bid number:]));

Thanks once again for spending time on this!
Kevin