Check for existing record then open form and populate

[lynthel]

I have a form that asks a user for a number (5 digits). I have an "on enter" macro that needs to check for an existing record. If an existing record is found, a different form will open and be populated with a table that is filtered with the 5 digit number. Or else it will open a separate form for user to input info to create a record for the 5 digit number.

I am getting a 2001 error when macro runs. I have attached a screen shot of the macro.

Any ideas would be greatly appreciated!

 IF (DCount("[tiw_program_#]","[tbl_programs]","[tiw_program_#]=" & "[txtProgramQuery]")>0)

[June7]

Remove the quote marks that are enclosing the textbox name. The quote marks cause the name to be treated as literal text, not a variable input.

Is [tiw_program_#] a number data type field?

BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[lynthel]

Thanks for the advice...will switch "#" to "no"...[tiw_program_#] is a 5 digit number that is defined as "number"...but now I am getting a "Type Mismatch" error

DCount("[tiw_program_#]","[tbl_programs]","[tiw_program_#]=" & [Forms]![frm_travelers_programs_blank]![txtProgramQuery])>0

[June7]

I don't use macros so am wildly guessing why this has issue.

Is this code behind [frm_travelers_programs_blank]? Perhaps the form name reference is not required.

Do you want the message box to say: "Switching to Program From"?

[lynthel]

"Switching to Program Form"....I just added message box code so I could see if the macro was working...how would you recommend I work around the macro? I am currently learning c#, have a little Java and HTML behind me, but really need to get the Access forms to work as desired...

[June7]

I use only VBA.

Are you positive tlw_program_no is a number type field?

[Subwind]

What is the difference between the populated form layout and the blank form layout? Can you not just use the same form, if data exists, it is populated, if not, starts a new record....

~Matt

[lynthel]

If (DCount("[tiw_program_no]","[tbl_travelers_programs]","[tiw_program_no]=" & [Forms]![frm_travelers_programs_blank]![lstProgramQuery])>0)

I finally got it to work...I had the wrong item in the second part of the If Statement...
Now to brush up on how to pass a filter of the selected program to the form...

Thanks for your help!