Access 2003 Query help

April 18, 2009 at 11:11:04
Specs: Windows XP
I am a bit of a beginner with access but am picking it up pretty quickly. I need to get my form to lookup a structure name and return it to a text box by typing in the corresponding structure no. I have all the details in a query but not sure what to do next.
Thanks

See More: Access 2003 Query help

Report •


#1
April 24, 2009 at 11:59:04
Could you show please what VBA code you have at this point, Anthony?

Report •

#2
April 24, 2009 at 12:06:02
Anthony, does this help you in some way by chance?

 Dim db As DAO.Database
 Dim rs As DAO.Recordset
Dim strSQL As String
 Dim strList As String
 
Set db = CurrentDb
 
strSQL = "SELECT  Products.ProductName " & _
"FROM    Products " & _
 "INNER JOIN Categories " & _
 "ON  Products.CategoryID      = Categories.CategoryID " & _
 "WHERE Categories.CategoryName='Beverages'; "
 
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With rs
Do While Not .EOF
strList = strList & rs!ProductName & ", "
.MoveNext
Loop
End With
 
 'Trim last comma
 If Right(strList, 2) = ", " Then
strList = Left(strList, Len(strList) - 2)
End If
 
Me.Text0 = strList
Set rs = Nothing
Set db = Nothing

In the VBA code, you can basically run your query and put the result(s) of the query into a record set. Then you can do whatever with the record set.

Me.txtBoxName.Text = rs!NameOfFieldFromQueryResult

I do not know though if I understand your situation correctly.


Report •

#3
April 24, 2009 at 12:24:06
Or maybe

formName.txt_NameOfTextBox.Value = rs!NameOfFieldInQuery 


Report •

Related Solutions

#4
June 17, 2009 at 11:54:45
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb

strSQL = "SELECT * FROM All_record_New WHERE (((All_record_New.[Physical Zip - US Only])=[Forms]![All_record1]![ZIP]) AND ((All_record_New.[Est Tot Wireline Communications Bill - Most Likely]) >= [Forms]![All_record1]![Est1] AND (All_record_New.[Est Tot Wireline Communications Bill - Most Likely])<= [Forms]![All_record1]![EST2]) AND ((All_record_New.[Emp Here]) >= [Forms]![All_record1]![EMP1] AND (All_record_New.[Emp Here]) <= [Forms]![All_record1]![EMP2]) AND And ((All_record_New.[Physical Street Address Line 1]) LIKE ('*'+[Forms]![All_record1]![FPA]+'*')));"
Set qdf = dbs.CreateQueryDef("qry" + Forms!All_record1!ZIP, strSQL)
DoCmd.OpenQuery ("qry" + [Forms]![All_record1]![ZIP])


Report •


Ask Question