Filling in subform fields with SQL query

[alley]

Using a form to display related recordset, and filling in 2 fields from a 3rd table.<br>
DougP will recognize code he supplied to someone else for this task. Form shows a job header, and the subform shows customer job detail by unique jobno. Each customer has his bill/pay rates for each task (job detail). The SQL uses custid and task code # in the detail record, to get the bill/pay rates from BillPay table.<br>
<br>
The following code is what I am trying for the task: (yard = custid)<br>
------------<br>
Private Sub jpay_LostFocus()<br>
Dim db As Database, rst As Recordset, SQL As String<br>
Set db = CurrentDb<br>
' SQL string.<br>
SQL = &quot;SELECT * FROM BILLPAY WHERE yard = &quot; & Me!yard & &quot; AND code = &quot; & Me!code & &quot;;&quot;<br>
&gt; Set rst = db.OpenRecordset(SQL)<br>
Me!jbill = rst!bill<br>
Me!jpay = rst!pay<br>
rst.Close<br>
db.Close<br>
<br>
Exit_Seek0:<br>
Exit Sub<br>
<br>
Err_Seek0:<br>
MsgBox &quot;Update Rate Table&quot;<br>
Resume Exit_Seek0<br>
End Sub<br>
-------------<br>
Besides a headache, I get error 3061. Too few Param-- expected (1). The &gt; above is the offending indicator, but I suspect the problem is earlier.<br>
<br>
BILLPAY is the 1 side of a 1 to many relation<br>
<br>
Any suggestions that won't harm me?<br>
alley

 

[DougP]

Put a stop F9 on this line&gt;&gt;&gt;Set rst = db.OpenRecordset(SQL)<br>
Then Press ctrl-G to open the debug window<br>
Then type ?SQL and press the enter key<br>
It will show the contents of the SQL string.<br>
Examine it closely...<br>
If the field &quot;yard&quot; is a string then you need single quotes aroung the value like so:<br>
SQL = &quot;SELECT * FROM BILLPAY WHERE yard = '&quot; & Me!yard & &quot;' AND code = '&quot; & Me!code & &quot;';&quot;<br>
<br>
Look after yard = '&quot; &lt;&lt;&lt;&lt;&lt;&lt; see the single quote<br>
<br>
Also around the &quot;code&quot; if its a string.<br>
<br>
OK<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[alley]

Thanks, DougP. It works perfectly. The syntax in VB is a killer. The single quotes(')<br>
isn't intuitive,logical or sensible. Is there a good reference book on VB syntax? How long did it take you to scramble your brains to make it seem remotely correct?<br>
<br>
Thanks again. alley

 

[JimConrad]

It starts to make sense when you think about it...<br><FONT FACE=monospace><br>WHERE field1 = field2&nbsp;&nbsp;&nbsp;&nbsp;' Compare two fields<br><br>WHERE field1 = 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' Compare to literal number<br><br>WHERE field1 = 'field2'&nbsp;&nbsp;' Compare to the string &quot;field2&quot;<br></font> <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>

 

[alley]

Jim, <br><br>I tried really hard to make sense of your samples, and could not get past this;<br><br>Your example 1 is comparing two fields, which is what I was doing in the Sub;<br>Traditionally that is the classic field comparison. whether or not the contents are<br>string.<br><br>Your example 2 is also traditional field/literal comparison, &quot;5&quot; applying if field1 is string<br><br>However, example 3 is the same as example 2, except those blasted single quotes.<br><br>In DougP's answer, He added <font color=red> ' </font> around <font color=red> &quot; </font> just because the values in the fields were<br>strings.&nbsp;&nbsp;Go figure.&nbsp;&nbsp;I believe VB's interface to SQL caused this requirement, even though no external reason is apparent.<br><br>My task is to learn the hierarchy of field descriptors that get me the data I want, after I have successfully described, opened, searched and extracted what I need from local, remote, or fictitious locations.<br><br>The real problem is I started 25 years too soon.&nbsp;&nbsp;Jim Conrad, thanks for your response;&nbsp;&nbsp;I'm still a skeptic.&nbsp;&nbsp;When it makes sense I'm not sure what I will do.<br><br>Alley

 

[JimConrad]

<FONT FACE=monospace><br>You almost have it.&nbsp;&nbsp;You are correct that examples 2 and 3 are similar (so why no quotes around the &quot;5&quot; in example 2?).<br><br>The answer is that a field name must be a string with at least one non-numeric character.&nbsp;&nbsp;In other words, you can call a field &quot;1st&quot; but not &quot;123&quot;.<br><br>So, Access &quot;knows&quot; that the 5 is a literal (even without the quotes).&nbsp;&nbsp;The same cannot be said of field2 (is it the literal we wish to compare or the value of a field called field2?).&nbsp;&nbsp;So we assist by surrounding string literals with quotes.&nbsp;&nbsp;Normally we would use double-quotes (like &quot, but since we are building a quoted string, we must either use two double-quotes in a row (&quot;&quot, or Access allows us to use a single quote (') within a quoted string (boy, this is sounding confusing even as I type it!).&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br></font> <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>