Select SQL statement

[mcinnes01]

Hi,

I am a total newbie with access databases, litterally 2nd day. I am trying to populate a textbox and the related control source table field with a select criteria.

Basically on the form you select a Job Title from a combobox then enter a division and I am trying to get it to select the "Code" that relates to that job.

The job details are held in a database view [TMC_PDT_DETAILS]

The forms source is the [PAFTable] but the actual values I want to refer to come from the form controls [JobTitle] and [Div].

I referenced these first then included them in my SQL string, but I keep getting various errors (I have tried many things)

I have tried both docmd.RunSQL and docmd.openQuery the later gets a 7874 error and the first gets 3075.

Private Sub Div_AfterUpdate()
Dim SQLstr As String, Job As String, Division As String
Job = JobTitle
Division = Div
SQLstr = "SELECT [TMC_PDT_DETAILS].Code " & _
         "AS F2 " & _
         "FROM [TMC_PDT_DETAILS] " & _
         "WHERE [TMC_PDT_DETAILS].[Job Title]= """ & Job & "" & _
         "AND [TMC_PDT_DETAILS].[Division Code] = """ & Division & """;"
DoCmd.RunSQL SQLstr
JobCode = F2
End Sub

Nb I have had to remove the square brackets round .Code as it opened the code quotes from there

 

[DCrake]

You cannot use DoCmd.RunSQL on a SELECT type query. the query must be an Action type query (Append/Delete/Update).

 

[mcinnes01]

Im getting a 3075 error now:

What this is meant to do is UPDATE the PAFTable and SET PAFTable.JobCode = TMC_PDT_Details.Code on the record WHERE PAFTable.PAFID = PAFForm.PAFID FROM TMC_PDT_Details WHERE TMC_PDT_Details.Job Title = PAFForm.JobTitle AND TMC_PDT_Details.Division Code = PAFForm.Div

This is my code

Private Sub Div_AfterUpdate()
Dim SQLstr As String, Job As String, Division As String, PID As String
PID = PAFID
Job = JobTitle
Division = Div
SQLstr = "UPDATE [PAFTable] SET [PAFTable].[JobCode] = [TMC_PDT_DETAILS].{Code} " & _
         "WHERE [PAFTable].[PAFID] = """ & PID & "" & _
         " FROM [TMC_PDT_DETAILS] " & _
         "WHERE [TMC_PDT_DETAILS].[Job Title]= """ & Job & "" & _
         " AND [TMC_PDT_DETAILS].[Division Code] = " & Division & " ;"
DoCmd.RunSQL SQLstr
End Sub

NB again the Code in square brackets I have represented with {Code}

 

[mcinnes01]

Can you point out where I am going wrong, as originally stated I tried both docmd.runSQL and docmd.openQuery.

I have now tried seperating the 2 parts in order to update the JobCode value on the Correct PAFID record

I still am doing something wrong, any ideas?

Option Compare Database
Private Sub Div_AfterUpdate()
Dim SQLstr1 As String, SQLstr2 As String, Job As String, Division As String, PID As String, JCode As String
PID = PAFID
Job = JobTitle
Division = Div
SQLstr1 = "SELECT {Code} AS J1 FROM [TMC_PDT_DETAILS] WHERE [JOB TITLE] = """ & Job & """ AND [Division Code] = """ & Division & """ ;"
DoCmd.OpenQuery SQLstr1
PCode = [TMC_PDT_DETAILS].J1
SQLstr2 = "UPDATE [PAFTable] SET [JobCode] = """ & JCode & """ WHERE [PAFID] = """ & PID & """ ;"
DoCmd.RunSQL SQLstr2
End Sub

again {code} represents square brackets

 

[mcinnes01]

Worked it out....

Private Sub Div_AfterUpdate()
Dim rs As Recordset
Dim SQLstr1 As String
Dim JCode As String
Dim Job As String, Division As String

On Error GoTo Textit
Job = JobTitle
Division = Div


SQLstr1 = "SELECT {Code} AS J1 FROM [TMC_PDT_DETAILS] WHERE [Job Title] = """ & Job & """ AND [Division Code] = """ & Division & """ ;"
Set rs = CurrentDb.OpenRecordset(SQLstr1)
    If rs.RecordCount > 0 Then
           JCode = rs("J1")
           JobCode = JCode
    Else
           MsgBox ("NO MATCH")
    End If
Textit: Exit Sub
End Sub