Refresher - How to get SQL to find data from an access form

[kyprogrammer]

So in a nutshell my brain is fried. I need help I am getting a too few parameters error when my set rs = db.OpenRecordset(mySQL) is compiled

Here is the mySQL variable

mySQL = "SELECT ASSY_QC_Check_Info.ID, ASSY_QC_Check_Info.Part_ID, ASSY_QC_Check_Info.Check_Point, ASSY_QC_Check_Info.Check_Type, ASSY_QC_Check_Info.Min, ASSY_QC_Check_Info.Max, " _
& "ASSY_QC_Check_Info.Desc_Area, ASSY_QC_Check_Info.Temp_Part_ID " _
& "FROM ASSY_QC_Check_Info " _
& "WHERE (((ASSY_QC_Check_Info.Part_ID)=[Forms]![QC_ASSY_Check_Main_frm]![txt_PartID]));"


I know the issue is because of what comes after the WHERE statement. However, the Part_ID is what filters down the recordset. When I create a query in the access database and paste the SQL code it does what it's supposed to. I am trying to do this programmically so it will auto populate a series of textboxes with the data.

TIA

J

 

[kyprogrammer]

This might work. Maybe my brain is coming back online.

Public Sub LoadmyTechVals2()
Dim mySQL As String
Dim myID As Integer

myID = [Forms]![QC_ASSY_Check_Main_frm]![txt_PartID]

mySQL = "SELECT ASSY_QC_Check_Info.ID, ASSY_QC_Check_Info.Part_ID, ASSY_QC_Check_Info.Check_Point, ASSY_QC_Check_Info.Check_Type, ASSY_QC_Check_Info.Min, ASSY_QC_Check_Info.Max, " _
& "ASSY_QC_Check_Info.Desc_Area, ASSY_QC_Check_Info.Temp_Part_ID " _
& "FROM ASSY_QC_Check_Info;"
'Debug.Print mySQL

Set db = CurrentDb
Set rs = db.OpenRecordset(mySQL)


Do While Not rs.EOF
rs.Filter = "Part_ID = " & myID

Loop


Set rs = Nothing
Set db = Nothing


End Sub

 

[Cronk]

I see you have a debug.print line commented out. That in my opinion is the way to go in debugging sql statement generation. Paste the results into a new sql query to see what your asking Access to do.

BTW, I presume there's other code in your loop because as it stands, the loop does nothing.

 

[Galaxiom]

& "WHERE (((ASSY_QC_Check_Info.Part_ID)=[Forms]![QC_ASSY_Check_Main_frm]![txt_PartID]));"

I know the issue is because of what comes after the WHERE statement. However, the Part_ID is what filters down the recordset.

Opening a recordset is a method of the database. As such, Access objects such as forms and the controls on them are outside the scope.

The value of the control needs to be concatenated into the sql string.

 

[MarkK]

Also, if you only have one table then you don't need to qualify every field name, so you could do...

    mySQL = _
        "SELECT ID, Part_ID, Check_Point, Check_Type, Min, Max, Desc_Area, Temp_Part_ID " & _
        "FROM ASSY_QC_Check_Info " & _
        "WHERE Part_ID = " & Forms!QC_ASSY_Check_Main_frm!txt_PartID

Also, google "Camel Case" and do that more, and use underscores less, and your code becomes much more readable.

    mySQL = _
        "SELECT ID, PartID, CheckPoint, CheckType, Min, Max, DescArea, TempPartID " & _
        "FROM AssyQCCheck " & _
        "WHERE PartID = " & Forms!fQcAssyCheckMain!txtPartID

Cheers,