VBA excluding (Ignoring) Comma

[RedWolf3x]

Ok so here is the problem. I currently have a form with a combo box. And several text boxes. The combo box is tied to a list of Names (Formatted "Lastname, Firstname")The Text boxes pull data from a table based off the name in the drop down box. This works fine using the next record or search feature. I'm trying to write a macro so that once a name is selected in the combo box it automatically updates the text boxes. Below is the code I'm using, however I get an error because all of the names have a comma in them. How can I get VBA to ignore the comma?

 
DoCmd.FindRecord DLookup("CSRName", "CSR Stats", "CSRName =" & Forms![Statistical Score Form]!Combo293.Value & ""), , True, , True

Thanks.

-Red

[Toyman]

Ok so here is the problem. I currently have a form with a combo box. And several text boxes. The combo box is tied to a list of Names (Formatted "Lastname, Firstname")The Text boxes pull data from a table based off the name in the drop down box. This works fine using the next record or search feature. I'm trying to write a macro so that once a name is selected in the combo box it automatically updates the text boxes. Below is the code I'm using, however I get an error because all of the names have a comma in them. How can I get VBA to ignore the comma?

 
DoCmd.FindRecord DLookup("CSRName", "CSR Stats", "CSRName =" & Forms![Statistical Score Form]!Combo293.Value & ""), , True, , True

Thanks.

-Red

Try this:
DLookup("CSRName", "CSR Stats", "CSRName ='" & Forms![Statistical Score Form]!Combo293.Value & "'"), , True, , True

[boblarson]

Well, first thing to address is this incorrect syntax:

"CSRName =" & Forms![Statistical Score Form]!Combo293.Value & "")

It should be:

"CSRName =" Chr(34) & Forms![Statistical Score Form]!Combo293.Value & Chr(34))

You have to have the quotes for delimiting text values. If CSRName is text then you need those. However, if CSRName is text and the combo is selecting a NUMBER (ID) then you need to refer to the right combo of the combo box to go with the text:

"CSRName =" Chr(34) & Forms![Statistical Score Form]!Combo293.Column(1) & Chr(34))

Where Column(1) is the second column as it is zero-based.

Also, you can use single quotes instead of Chr(34) but I find those to be problematic with some names like O'Hara, O'Neil, etc. Or you can use triple double quotes (""") but that quickly gets hard to read. So I like using Chr(34) instead.

[RedWolf3x]

Thank you both for your help, unfortunately I was not able to get either of those to work properly (I blame my poor VBA experience). However I completely missed the fact that what I was trying to accomplish was already a built in function of Access. So I've gotten it to work. Thank you both again.

-Red