"missing operator" error passing multiple field values to another form to open

[saraqpost]

I want to pass the customer name (first and last) and the ID for the
customer from the customer form to the items form, so the user can add
a new item for the customer.

I keep getting the message "missing operator" - but the strLinkCriteria
looks right to me.

The txtCustomer box on the frmItem is unbound, named txtCustomer. I
have tried =FirstName & " " & LastName for the control source for the
field, and once I got the form to open (I took off open as DE) but the
field said "#Name".

Can someone tell me what I'm missing?

' Open frmItems in DE mode
' Bring over the Customer name and ID to the form

Dim lngCustID As Long
Dim txtFirstName As String
Dim txtLastName As String

Dim stLinkCriteria As String
Dim stDocName As String

lngCustID = Me.lngCustID
txtFirstName = Me.txtFirstName
txtLastName = Me.txtLastName


stDocName = "frmItems"
stLinkCriteria = "CustID =" & lngCustID & " AND txtCustomer =" _
& txtFirstName & " " & txtLastName


DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Thank you very much.

 

[Klatuu]

I don't know if this will solve the problem, but one thing I noticed is
missing quotes where the value you want to compare to is text. In this
context you can use a single quote (') to avoid the old how many quotes do I
string together to get this %%#%HR$^$&*^&!!! query to work! problem.

stLinkCriteria = "CustID =" & lngCustID & " AND txtCustomer = '" _
& txtFirstName & " " & txtLastName & "'"

 

[saraqpost]

Why thank you!! That did work! I am dissecting it to understand, but
I think the rule is that since I am concatenating data I need single
quotes around the "entire field" - as part of the expression, where the
double quotes are just syntax. Sound right?

NOW, the form opens, but the Customer's Name is not in the field on the
form when it opens. It says "#Name". Any tips on that one?

Thx

 

[Klatuu]

You are correct. The double quotes define the string and the single quotes
define the criteria values.
Do you have a Control Source for your customer name (txtCustomer)? Post
back with what you have there, and I think we can fix that, too.

 

[John Vinson]

Why thank you!! That did work! I am dissecting it to understand, but
I think the rule is that since I am concatenating data I need single
quotes around the "entire field" - as part of the expression, where the
double quotes are just syntax. Sound right?

You're close. What you're doing is constructing a string out of pieces
- some of the pieces are constant, unchanging boilerplate text (such
as fieldnames and operators); other pieces are variables, in this case
from a form. Your string:

stLinkCriteria = "CustID =" & lngCustID & " AND txtCustomer = '" _
& txtFirstName & " " & txtLastName & "'"

is combining the pieces

CustID =
3190 // assuming a value for the ID
AND txtCustomer = '
Fred
<a blank>
O'Hare
'

to get the expression

CustID = 3190 AND txtCustomer = 'Fred O'Hare'

WOOPS! There's a problem. The ' in the name O'Hare will be seen as the
end of the string! See below.

One BIG question here: well, two related questions. It looks like
you're doing a "belt and suspenders" search, searching for the value
of a field named CustID (from a VBA variable), and - among the results
for that ID - searching a field named txtCustomer in the table for a
particular first and last name. Somehow I suspect this is NOT what you
intend. What *are* you using as the search criterion? Just the ID? Are
you in fact *searching* using the name fields in txtFirstName and
txtLastName as criteria against a table field; or are you instead
intending to *display* that concatenation on the Form or Report?

NOW, the form opens, but the Customer's Name is not in the field on the
form when it opens. It says "#Name". Any tips on that one?

It sounds like my guess is right: and that there is no Table field
named txtCustomer.

I'd suggest removing everything after lngCustID from strLinkCriteria,
and setting the Control Source property of a textbox on your report to

= [FirstName] & " " & [LastName]

(assuming that you have table fields with these names).

John W. Vinson[MVP]

 

[saraqpost]

WOW! Getting there - can update, but not add records.

I understand how to create the link criteria now - text values need to
be surrounded in single quotes, numbers don't need quotes, and the
"literals" need double quotes. This makes sense - I would see my
criteria with a MsgBox, but the single quotes are very small and not
very black and I wasn't clear on their role.

Yes, I don't have a table field called txtCustomer; I do have FirstName
and LastName. I just want to display the customer's name on the form
so the user knows they are working on the right customer.

BUT, the form I'm opening is "customer items" (frmItems) and it is
bound to the Customer Items table (tblCustomerItems), which doesn't
have the customer name in it. Does this mean that I should bind the
form to a query and pull those fields in? Then I would not need to
send the fields - they'd be accessible via the key (CustID) field in
the query?

One thought as I'm starting to figure this all out - I don't have any
of these problems in reports - I make sure all the fields I want on a
report are in the query on which the report is based. Does the same
rule apply to a form? Use a query when all the fields you want to see
or update aren't in one table (as they would rarely be, with normalized
data)? How does this affect updating and adding records? This narrows
down my area of confusion - clarifies the confusion for me, if you
will.

I tested this and changed the form to be bound to a query. I get the
name to show when I open the form for edit, and edits work. But when
I open the form for Adds, no name appears and I can't update. The
query is the problem "Cannot add record(s);Join key of table
tblCustomerItems not in recordset."

I am joined on CustID (one to many). I tried including all customers
and only CustItems where the key matched, but that caused ambiguous
outer join.

So, I can't add items. I have a button on the form "add Item" that
opens frmItems in DataEntry mode. When I bind the form to the table,
or a query on just the Customer Items table, I can add, but don't get
the customer name on the form. Does this mean I can't use the one form
and change its properties (to DataEntry, for example) when I want to
add items?

Really appreciate the help. I have learned so much in these few emails!
I'm enjoying this a bit more now, too.
sara