Set rs = DB.OpenRecordset trouble

[bailey11]

I have a form for equipment time entry that I am having a problem with.

I can't get past this code:

Set rs = DB.OpenRecordset("SELECT * FROM tblequipcat WHERE EquipmentNo = " & Me.EquipIDTE, dbOpenForwardOnly)

"tblEquipCat" hold the master files for the equipment we use.

"EquipmentNo" is the field identifying the piece of equipment. It is a text field (alphanumeric).

"Me. EquipIDTE" is the field on the equipment time entry form that eqivalates to the EquipmentNo in the tblEquipCat.

When I get to this code, it causes the code to cancel, but I can't figure out why.

Also, if I open the form and then try to view it in design, it causes a reportable error and shuts Access down.

Help. I have an identical form for Payroll time entry and it works fine, all I am doing is changing table and field names basically.

Help please.

 

[gwinn7]

You said that Me.EquipIDTE was an alphanumeric. Your code does not display that you have qualified your string with proper quotations. So, I suggest it should be instead...

("SELECT * FROM tblequipcat WHERE EquipmentNo = '" & Me.EquipIDTE & "'"

Secondly, one of the first steps to troubleshoot a problem like that is to make sure your SQL will work in a Query. So, just dump a sample of what you are expressing above in the Query Builder in SQL mode and test it.

Plus, when you report a problem, it is most helpful to provide the error message that you said was "reportable". I have found that Access is not the most stable piece of work! There have been numerous times over the years where I have inadvertantly caused a code module to corrupt that would crash Access. To fix that I suggest this...

Grab your computer with both hands, open a window, and... Well, ok, I was just kidding...

Seriously, try creating another MDB and re-import all your code modules into it. See if you can edit the form's code module without crashing Access. Then, believe it or not, add arbitrary spaces between code lines and re-compile your database. That has fixed some of my code corruption issues. Otherwise, you may be facing a complete rebuild of your form from scratch.

Hope that helps,

Gary
gwinn7

 

[gwinn7]

Also, think about turning off Auto-Complete option, it has been known to corrupt code modules and cause Access to crash. I know from first hand experience.

Gary
gwinn7

 

[bailey11]

Thanks to all, I can get past that now.