Field name confusion

Question

rs2.FindFirst "[aniin] ='" & strTemp & "'"

aniin being an alias from the SQL within the function.

also tried ...

rs2.FindFirst (niin = newdata)

is my attempt to isolate the field name niin from the record value in the form from the one in the strSQL2. All my attempts have failed. I am trying to make sure that what the user typed in does match the list from the SQL string.

Private Function IsPartOfAEL(newdata) As Boolean

On Error GoTo ErrTrap
Dim db2 As DAO.Database
Dim rs2 As DAO.Recordset
Dim strTemp As String

strSQL2 = "SELECT tbl_ael_parts.master_ael_id, tbl_master_niin.niin as aniin " & vbCrLf & _
"FROM tbl_master_niin INNER JOIN tbl_ael_parts ON tbl_master_niin.master_niin_id = tbl_ael_parts.master_niin_id " & vbCrLf & _
"WHERE (((tbl_ael_parts.master_ael_id)= " & Forms!frm_qry_niin_local!master_ael_id & "));"

Set db2 = CurrentDb
Set rs2 = db2.OpenRecordset(strSQL2)
strTemp = newdata
If rs2.RecordCount <> 0 Then
    rs2.FindFirst "[aniin] ='" & strTemp & "'"
    If rs2.NoMatch Then
        IsPartOfAEL = False
    Else
        IsPartOfAEL = True
    End If
Else
    MsgBox "Query Returned Zero Records", vbCritical
    Exit Function
End If

rs.Close

Set rs2 = Nothing
Set db2 = Nothing

ExitHere:
    Exit Function
ErrTrap:
    MsgBox Err.description
    Resume ExitHere

End Function

What do you mean by "your attempts have failed"? How are they failing? — Smandoli, Jul 11 '14 at 21:26
The 'niin' value being evaluated in the FindFirst is from the form not the SQL2 query. The vbCrfl is from Allen Brown on the subject of SQL in VBA so I think it is safe. I thought about DCount but wanted to learn from this if it can be done. Seems the only issue is Access getting confused about 'niin'. — Kaw4Life, Jul 14 '14 at 10:47
Scott the SQL is not being accepted. I added AND niin=" & newdata & ");" to the end of mine and it took it. Now though I am getting too few parameters. — Kaw4Life, Jul 14 '14 at 12:08
Now you need to trouble shoot by taking that complete SQL string and running it in ordinary Access query view. From there it is easier to see what is wrong. More info at http://stackoverflow.com/a/1099570/122139. — Smandoli, Jul 14 '14 at 13:12
Access query view does not like 'newdata' as it is only avail during the NotInList event. I have it working in the the event but I think I need to address the 'parameter' issue. — Kaw4Life, Jul 14 '14 at 17:12
To see how your parameters are handled, including the value of `newdata`, you must catch the SQL as it is generated: `Debug.Print(strSQL2)`. This is what the linked post explains. — Smandoli, Jul 14 '14 at 17:29
Did as advised. ... expected 'DELETE','INSERT','PROCEDURE','SELECT' or 'UPDATE'. The SQL sting I fed it does not give me any errors in the Access Query View window. — Kaw4Life, Jul 14 '14 at 18:27
You would have to capture `strSQL2` just before it is run. Just above the line `Set rs2 = db2.OpenRecordset(strSQL2)`. Then you would see an SQL string. — Smandoli, Jul 14 '14 at 20:13
Did that, what am I looking for? Only value showing is 'newdata'. — Kaw4Life, Jul 15 '14 at 10:50
What you are looking for is a SQL string. Please understand that when are able to catch `strSQL2` and find it is a SQL string, your problem will be solved. I suggest you just keep working at it; from here, I probably can't help you any further. — Smandoli, Jul 15 '14 at 12:49

score 1 · Accepted Answer · answered Jul 11 '14 at 21:09

First: You should never include a constant like vbCrLf when building a query string. The query parser doesn't care if there's a linefeed, and in fact this can sometimes cause issues.

Your code seems to do nothing more that verify whether the value in newdata exists in the tbl_ael_parts and is associated with the value master_ael_id value currently showing on frm_qry_niin_local. If so, then just use DCount, or use this for your query:

strSQL2 = "SELECT tbl_ael_parts.master_ael_id INNER JOIN tbl_ael_parts ON
  tbl_master_niin.master_niin_id = tbl_ael_parts.master_niin_id WHERE (((tbl_ael_parts.master_ael_id)= 
  " & Forms!frm_qry_niin_local!master_ael_id & ") AND niin=" & newdata & ");"

Dim rst As DAO.Recordset
Set rst = currentdb.OPenrecordset(strsql2)

If (rst.EOF and rst.BOF) Then
   ' no records returned
Else
   ' records found
End If

If niin is a Text field:

strSQL2 = "SELECT tbl_ael_parts.master_ael_id INNER JOIN tbl_ael_parts ON
  tbl_master_niin.master_niin_id = tbl_ael_parts.master_niin_id WHERE (((tbl_ael_parts.master_ael_id)= 
  " & Forms!frm_qry_niin_local!master_ael_id & ") AND (niin='" & newdata & "'));"

If both niin and master_ael_id are Text fields:

strSQL2 = "SELECT tbl_ael_parts.master_ael_id INNER JOIN tbl_ael_parts ON
  tbl_master_niin.master_niin_id = tbl_ael_parts.master_niin_id WHERE (((tbl_ael_parts.master_ael_id)= 
  '" & Forms!frm_qry_niin_local!master_ael_id & "') AND (niin='" & newdata & "'));"

I have never heard of an issue cause by use of `vbCrLf`. It is helpful to have line-feeds when catching the SQL string for trouble-shooting; I often recommend it. — Smandoli, Jul 11 '14 at 21:24
vbCrLf is a UI convention, and IMO has no place in a query string. I've had issues with other constants (like vbTab) when inserted into a query string to format them for easier human readability. But, as I said, that's just my opinion. — scottmcd9999, Jul 11 '14 at 21:53
Yeah, good answer, but I also think the claim that vbCrLf in SQL strings cause issues is bunk. Have never run into an issue because of it. No need to be dogmatic about it. — VBlades, Jul 12 '14 at 21:03
Afraid none of this is working. Core problem is still not resolved. This is a parameter query that is not being satisfied. I do not know how to feed the query what it needs. Going to abort for now. I have higher priorities. Thank you all for you effort. — Kaw4Life, Jul 15 '14 at 11:14
I have since learned a lot about the syntax of parameter query's. That was the problem the whole time. — Kaw4Life, Aug 07 '14 at 16:33

Field name confusion

1 Answers1