Writing query in access

[vba317]

I have two text boxes that I am trying to lookup some information with. The information I need is based on two filled in text boxes.
I have two textboxes one called txtModelName, the other txtProblem on a form called frmServiceCalls
The table the info is coming from is tblPossibleSolution
I wrote a query that does work

Private Sub txtDescriptionofEvent_Exit(Cancel As Integer)
SELECT TblPossibleSolution.ModelName, TblPossibleSolution.Problem, TblPossibleSolution.Solution
FROM TblPossibleSolution
WHERE (((TblPossibleSolution.ModelName)=[Enter Model Name]) AND ((TblPossibleSolution.Problem)=[Enter Problem]));
End Sub

This gave me a syntax error is there a way of using a query in vba? If there a way of using info from the form to get the answer? In this case the info is based on the modelname and the problem.

 

[SkipVought]

hi,

You need a string variable to store the SQL. Then it must be assigned to the recordset command string...

dim sSQL as string

sSQL = "SELECT TblPossibleSolution.ModelName, TblPossibleSolution.Problem, TblPossibleSolution.Solution " & _
"FROM TblPossibleSolution " & _ 
"WHERE (((TblPossibleSolution.ModelName)='" & [Enter Model Name] & "') AND " & _  "((TblPossibleSolution.Problem)='" & [Enter Problem] & "'));"

I would not recommend a TextBox for user entry of such values THAT MUST BE EXACT" Why not a ComboBox?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Skip,
Using your code I ams still getting a syntax error. Thank you for your recommendation for the model name I am using a Dlookup, I thought of using a combobox for the problem field but there will be too much information in it for it to be useful.

 

[dhookom]

I can't determine what you want to do with the returned fields/records? Is this for a record source for a form or a list box row source or what?

Duane
Hook'D on Access
MS Access MVP

 

[vba317]

I have a form that has a textbox called txtModelName this box is filled in from a Dlookup from another text box. What I need to happen is when an operator enters a problem in txtDescriptionofEvent textbox that an answer populates the textbox txtSolution. The answer should come from the tblSolution. Originally I wanted to use a Dlookup to do this but I didnt think you could use a Dlookup with two criteria.

 

[dhookom]

You can use DLookup() with multiple criteria. You should be able to set the control source of txtSolution to:

=DLookup("Solution","TblPossibleSolution","ModelName =""" & [txtModelName] & """ AND Problem =""" & [txtDescriptionofEvent] & """")

This requires exact matches of the model name and the description which I would think would be unlikely.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I didnt think you could use a Dlookup with two criteria
Sure you can.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I am glad you can use DLookup with two criteria. I got a sytax error using dhookom's code. Any suggestions?

 

[PHV]

Any suggestions?
Which code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

This is the code that has the symtax error

=DLookup("Solution","TblPossibleSolution","ModelName =""" & [txtModelName] & """ AND Problem =""" & [txtDescriptionofEvent] & """")

 

[vbajock]

=DLookup("Solution","TblPossibleSolution","ModelName =""" & [txtModelName] & """ AND Problem =""" & [txtDescriptionofEvent] & """")
does not compile because [txtModelName] and [txtDescriptionofEvent] are not valid identifiers. Replace them with some variable, literal, constant or field that represents the values you need.

 

[dhookom]

vba317,
Did you use the DLookup() in the control source of your text box?
Do you have two controls on the form with the names txtModelName and txtDescriptionofEvent?


Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

If he is, then he is getting an error because one or both of the fields [txtModelName] and [txtDescriptionofEvent] do not exist on his form, in other words.....

I kind of doubted that because his strSQL is in code, so I assumed his dlookup was in his code as well.

Of course, this will simply lead to his code blowing up again because he has no tests for nulls.

 

[dhookom]

vbajock,
Based on vba317's question(s) it's really difficult to understand how this is supposed to work since the "=" require exact matches. My solution simply avoided any VBA to meet the original need as I understood.


Duane
Hook'D on Access
MS Access MVP

 

[vbajock]

Prob just need to add Me! or the form name to avoid syntax error, not that I look more closely at it:

DLookup("Solution","TblPossibleSolution","ModelName =""" & me![txtModelName] & """ AND Problem =""" & me![txtDescriptionofEvent] & """")

 

[dhookom]

I always use "Me." or "Me!" in code to reference form controls or fields. These will cause issues when the DLookup() is used in a Control Source property.

Regardless, I would use combo and/or list boxes or similar on the form rather than text boxes which are not user friendly and prone to errors.

Duane
Hook'D on Access
MS Access MVP