How do you: Populate Form Field From Query?

[CheapEmployerNoSQL]

So I have a form that has a text box called [SalesRep] a button when clicked runs a query from parameters entered in the form which results in a new window with a single field/record [Rep]; I have fumbled around for days trying to google a solution becasue I do not know how to get the result [REP] into the form text box name:[SalesRep] control source: [Sales Rep]

Here is the code to run the query and then the query;

Private Sub Command40_Click()
On Error Resume Next
DoCmd.OpenQuery "findrentsysrep"
DoCmd.RunCommand acCmdSaveRecord
End Sub

SELECT RentsysReps.Rep AS Rep
FROM (RentsysReps LEFT JOIN LetterAssigned ON RentsysReps.ID=LetterAssigned.RepID) LEFT JOIN StateAssigned ON RentsysReps.ID=StateAssigned.RepID
WHERE (((RentsysReps.Type)=Forms!Rentsys_CallLog.Type) And ((LetterAssigned.LetterAssignment) Like Left(Forms!Rentsys_CallLog.CompanyName,1))) Or (((StateAssigned.StateAbb) Like Forms!Rentsys_CallLog.State));

 

[JANR]

SELECT queries is for viewing ONLY. If you want to pass values you have to use ACTION queries like Append queries or Update queries.

JR

 

[CheapEmployerNoSQL]

SELECT queries is for viewing ONLY. If you want to pass values you have to use ACTION queries like Append queries or Update queries.

JR

How do I update or append my form field to get the results from the select statement (query)?


Would the query be

Update "form?"
Set [SalesRep] = [Rep]

FROM

FROM (RentsysReps LEFT JOIN LetterAssigned ON RentsysReps.ID=LetterAssigned.RepID) LEFT JOIN StateAssigned ON RentsysReps.ID=StateAssigned.RepID
WHERE (((RentsysReps.Type)=Forms!Rentsys_CallLog.Type) And ((LetterAssigned.LetterAssignment) Like Left(Forms!Rentsys_CallLog.CompanyName,1))) Or (((StateAssigned.StateAbb) Like Forms!Rentsys_CallLog.State));

What would be the syntax?

 

[shez]

dont quote me on this but if you want the query to run based on parameters users can input via form then u shud do:


select * from table
where personalID = formname!formfield

 

[CheapEmployerNoSQL]

I have the WHERE part from the user entered data in th form and the query is returning the information I need, however I need to bridge my learning gap on how instead of selecting the result to view, get the result inserted into the form field.

 

[JANR]

No you update the underlying table and requery/refresh your form to see the update.
If your current query give you what you want then you can open it in designView and change it to an updatequerie. You will the see an extra row Update to:

Put in a refrence to your control on the form, Forms!NameOf YourForm!NameOfYourControl.

Caution: Actionquery's alter you data so TEST before you use it.

JR

 

[shez]

nevermind! i was probably talking rubbish!

 

[CheapEmployerNoSQL]

Ok; trying the UPDATE type on the query and entering "Forms!Rentsys_CallLog.SalesRep" in the Update To: under field "rep" caused not my form field "SalesRep" to contain the "rep" but it is updating the table sales rep by blanking out the rep that would have been the correct one:

UPDATE (RentsysReps LEFT JOIN LetterAssigned ON RentsysReps.ID = LetterAssigned.RepID) LEFT JOIN StateAssigned ON RentsysReps.ID = StateAssigned.RepID SET RentsysReps.Rep = Forms!Rentsys_CallLog.SalesRep
WHERE (((RentsysReps.Type)=[Forms]![Rentsys_CallLog].[Type]) AND ((LetterAssigned.LetterAssignment) Like Left([Forms]![Rentsys_CallLog].[CompanyName],1))) OR (((StateAssigned.StateAbb) Like [Forms]![Rentsys_CallLog].[State]));

I need the form to be updated with the rep from the query, not replace the rep by with empty text box data


So I need SET Forms!Rentsys_CallLog.SalesRep = RentsysReps.Rep

How do I write the update Forms!Rentsys_CallLog.SalesRep?

 

[JANR]

Caution: Actionquery's alter you data so TEST before you use it.

but it is updating the table sales rep by blanking out the rep that would have been the correct one

Yes because the query didn't find a value just a "blank" and therefor updated the field with a "blank" Try Forms!Rentsys_CallLog!SalesRep

This imply that the form is called "Rentsys_CallLog", and the control which hold yor updatevalue is called "SalesRep".

How do I write the update Forms!Rentsys_CallLog.SalesRep?

You DON'T write to forms!!! the forms just displays your data and interact with it, you write to tables.

JR

 

[CheapEmployerNoSQL]

JR I am really thankful you are helping me; I am not sure what you mean for me to try. I agree; My goal is not to write to the form but to the table Rentsys_CallLog but I do want to have the query (execution) display the "REP" in the form field.

So in order to do this the query needs to find the rep from the criteria and once found insert it into the current record field SalesRep and then have the form display the updated record with an entry in the SalesRep field "textbox"

 

[JANR]

Is it possible for you to post a samplebase to look at with the relevant tables, queries and forms?

JR

 

[CheapEmployerNoSQL]

I think the attachment will work to show the form, table, query

 

[JANR]

I have overthinked it again. It was a bit difficult to follow you logic, but if your first SQL was a way to filter down possible sales rep's for a spesific region and or status, the an easy fix is to use your first sql

SELECT RentsysReps.Rep AS Rep
FROM (RentsysReps LEFT JOIN LetterAssigned ON RentsysReps.ID = LetterAssigned.RepID) LEFT JOIN StateAssigned ON RentsysReps.ID = StateAssigned.RepID
WHERE (((RentsysReps.Type)=[Forms]![Rentsys_CallLog].[Type]) AND ((LetterAssigned.LetterAssignment) Like Left([Forms]![Rentsys_CallLog].[CompanyName],1))) OR (((StateAssigned.StateAbb) Like [Forms]![Rentsys_CallLog].[State]));

Your field SalesRep on your form, change it to a combobox and paste the SQL in the recordsource of the combobox. You have to include it in your taborder.

When the relevant criteriafields are filled out, possible salesrep's will show in the combobox, make your pick and since it's bound to your table the correct Rep is updated. But you have to think about "not in list" events and how to handle cases where no rep's qualifies.

JR

 

[JANR]

Almost forgot. Remove Private Sub Command40_Click() or change it to

Private Sub Command40_Click()
On Error Resume Next
Me.salesrep.setfocus
Me.salesrep.dropdown
End Sub

JR
Goodluck

 

[CheapEmployerNoSQL]

Awesome! This will work wonderfully! I do have some exceptions with the way Reps are assigned so if the result set has multiple choice then at least the user can make a choice.

Thank you!

 

[JANR]

Glad to help.

JR