Query record in SQL

  • Thread starter Thread starter Design by Sue
  • Start date Start date
D

Design by Sue

I have a form that uses a query to produce a combo box as a list for the user
to choose a PartNumber-Suffix combination (the query includes the fields
PartNumber and Suffix from the PartSuffixTbl and using Expr1 joins them with
a dash between them). The user is to enter a Line number in an unbound text
box. Then the button I created uses SQL to save the line number to the
PartSuffixTbl if the PartNumber and Suffix match that selected in the combo
box. My problem is that the only line number that gets updated is the first
record on my PartSuffixTbl. no matter what is selected in the combo box.
What is the correct language (path) to put in the SQL so that the programming
knows to check the query for the selected PartNumber-Suffix. I have tried
referencing the current form, the combo box and PartNumber (also Suffix). (ie
Forms![LocationFrm3]!]![PartNumber]). I also tried the current form,
PartNumber (also Suffix) (ie
Forms![LocationFrm3]![PartNumberCombo]![PartNumber]).

Any thoughts?
 
You need to filter your update query so it only updates the row for the
ParNumber and Suffix. Where you will run into troulbe is that you have the
field concatenated in the combo. You can still use the concatenated field
for display, but you should include them in the combo's row source so it will
be easy for the query to find them.
 
Thanks - they were in the combo's row source but it still was not working. I
just tried to create additional text boxes displaying the PartNumber and
Suffix based on the Query (which I will hide) and now the SQL works - at
least almost. It is now updating the selected PartNumber but is also (still)
updating the first record of the table everytime. I am using Me.refresh on
the button after the SQL so the form refreshes and an other text box that
displays the parts in the line selected will include the recently updated
PartNumber. It seems that this Me.Refresh is running the SQL again on the
refreshed form that now has reset itself to the first PartNumber. Confused?
I am. Thanks for your help - now on to this next problem.

Klatuu said:
You need to filter your update query so it only updates the row for the
ParNumber and Suffix. Where you will run into troulbe is that you have the
field concatenated in the combo. You can still use the concatenated field
for display, but you should include them in the combo's row source so it will
be easy for the query to find them.
--
Dave Hargis, Microsoft Access MVP


Design by Sue said:
I have a form that uses a query to produce a combo box as a list for the user
to choose a PartNumber-Suffix combination (the query includes the fields
PartNumber and Suffix from the PartSuffixTbl and using Expr1 joins them with
a dash between them). The user is to enter a Line number in an unbound text
box. Then the button I created uses SQL to save the line number to the
PartSuffixTbl if the PartNumber and Suffix match that selected in the combo
box. My problem is that the only line number that gets updated is the first
record on my PartSuffixTbl. no matter what is selected in the combo box.
What is the correct language (path) to put in the SQL so that the programming
knows to check the query for the selected PartNumber-Suffix. I have tried
referencing the current form, the combo box and PartNumber (also Suffix). (ie
Forms![LocationFrm3]!]![PartNumber]). I also tried the current form,
PartNumber (also Suffix) (ie
Forms![LocationFrm3]![PartNumberCombo]![PartNumber]).

Any thoughts?
Click to expand...
Click to expand...
 
OK - I turned on the navigation buttonsso I can see what record the form is
on and even though I am selecting a PartNumber-Suffix in the Combo Box, the
form itself is still on record one so when I am updating the information
through the SQL the programming is seeing the PartNumber-Suffix I selected in
the Combo Box AND the record that the form is on. So how do I remove the
navigation from the form so the only navigation is through the Combo Box?
Turning off the navigation buttons is not what I need.

Thanks
Sue

Design by Sue said:
Thanks - they were in the combo's row source but it still was not working. I
just tried to create additional text boxes displaying the PartNumber and
Suffix based on the Query (which I will hide) and now the SQL works - at
least almost. It is now updating the selected PartNumber but is also (still)
updating the first record of the table everytime. I am using Me.refresh on
the button after the SQL so the form refreshes and an other text box that
displays the parts in the line selected will include the recently updated
PartNumber. It seems that this Me.Refresh is running the SQL again on the
refreshed form that now has reset itself to the first PartNumber. Confused?
I am. Thanks for your help - now on to this next problem.

Klatuu said:
You need to filter your update query so it only updates the row for the
ParNumber and Suffix. Where you will run into troulbe is that you have the
field concatenated in the combo. You can still use the concatenated field
for display, but you should include them in the combo's row source so it will
be easy for the query to find them.
--
Dave Hargis, Microsoft Access MVP


Design by Sue said:
I have a form that uses a query to produce a combo box as a list for the user
to choose a PartNumber-Suffix combination (the query includes the fields
PartNumber and Suffix from the PartSuffixTbl and using Expr1 joins them with
a dash between them). The user is to enter a Line number in an unbound text
box. Then the button I created uses SQL to save the line number to the
PartSuffixTbl if the PartNumber and Suffix match that selected in the combo
box. My problem is that the only line number that gets updated is the first
record on my PartSuffixTbl. no matter what is selected in the combo box.
What is the correct language (path) to put in the SQL so that the programming
knows to check the query for the selected PartNumber-Suffix. I have tried
referencing the current form, the combo box and PartNumber (also Suffix). (ie
Forms![LocationFrm3]!]![PartNumber]). I also tried the current form,
PartNumber (also Suffix) (ie
Forms![LocationFrm3]![PartNumberCombo]![PartNumber]).

Any thoughts?
Click to expand...
Click to expand...
Click to expand...
 
OK - Never mind - this stuff makes me feel so stupid. I had to clear teh
record source for the form and now all works correctly. DUH!!!

Design by Sue said:
OK - I turned on the navigation buttonsso I can see what record the form is
on and even though I am selecting a PartNumber-Suffix in the Combo Box, the
form itself is still on record one so when I am updating the information
through the SQL the programming is seeing the PartNumber-Suffix I selected in
the Combo Box AND the record that the form is on. So how do I remove the
navigation from the form so the only navigation is through the Combo Box?
Turning off the navigation buttons is not what I need.

Thanks
Sue

Design by Sue said:
Thanks - they were in the combo's row source but it still was not working. I
just tried to create additional text boxes displaying the PartNumber and
Suffix based on the Query (which I will hide) and now the SQL works - at
least almost. It is now updating the selected PartNumber but is also (still)
updating the first record of the table everytime. I am using Me.refresh on
the button after the SQL so the form refreshes and an other text box that
displays the parts in the line selected will include the recently updated
PartNumber. It seems that this Me.Refresh is running the SQL again on the
refreshed form that now has reset itself to the first PartNumber. Confused?
I am. Thanks for your help - now on to this next problem.

Klatuu said:
You need to filter your update query so it only updates the row for the
ParNumber and Suffix. Where you will run into troulbe is that you have the
field concatenated in the combo. You can still use the concatenated field
for display, but you should include them in the combo's row source so it will
be easy for the query to find them.
--
Dave Hargis, Microsoft Access MVP


:

I have a form that uses a query to produce a combo box as a list for the user
to choose a PartNumber-Suffix combination (the query includes the fields
PartNumber and Suffix from the PartSuffixTbl and using Expr1 joins them with
a dash between them). The user is to enter a Line number in an unbound text
box. Then the button I created uses SQL to save the line number to the
PartSuffixTbl if the PartNumber and Suffix match that selected in the combo
box. My problem is that the only line number that gets updated is the first
record on my PartSuffixTbl. no matter what is selected in the combo box.
What is the correct language (path) to put in the SQL so that the programming
knows to check the query for the selected PartNumber-Suffix. I have tried
referencing the current form, the combo box and PartNumber (also Suffix). (ie
Forms![LocationFrm3]!]![PartNumber]). I also tried the current form,
PartNumber (also Suffix) (ie
Forms![LocationFrm3]![PartNumberCombo]![PartNumber]).

Any thoughts?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top