Compile Error (1 Viewer)

[chrismcbride]

I am really not sure what the Debug Menu command 'Complile All Modules' is supposed to do, however I am trying to use it as a way to test the integrity of the coding on my forms.
Anyway the problem I have is with compiling parts of my code that have SQL strings. For example I have a number of DoCmd.RunSql commands that create records automatically. The compiler will stop on a field name like...
me.inv_no and say "Method or Data member not found"
...
I have of course checked to see if the field name is correct (and it is) and furthermore the compiler will allow the same reference in a later bit of code (only to trip on another field name).

I imagine that there is some problem with the SQL string itself, however they run in the procedures that hold them and, once they have been adapted to run from the QBE, they also run. Could it have something to do with the enclosing of strings within the SQL string. Or can any one think of some other reason?
I have enclosed a string that the compiler trips on...

DoCmd.RunSQL "INSERT INTO tblInvoice (cust_no, admin_no, cust_po, inv_date, " _
& "file_no, inv_address, inv_exchange, void, credit_note, gst, pst, ship, " _
& "discount, cfi, id_string, cont_no, inv_cur) VALUES (" _
& Me.cboCustomer & ", " & Me.cboAdmin & ", '" & Me.cust_po & "', #" _
& Me.inv_date & "#, '" & Me.id_string & Me.inv_no & "', " & Me.inv_address _
& ", " & Forms!frmConfig_Payable_Invoice!invExchange _
& ", 0, -1, " & Me.chkGST & ", " & Me.chkPST & ", " & Me.ship _
& ", " & Me.discount_rate & ", " & Me.cfi & ", 'CRE-', " & Me.cont_no _
& ", " & Me.inv_cur & ");"


By the way it may help to know that in this case the compiler trips on the reference to...
me.invExchange
... In fact, I have tried to rename the feild, with no change.

Thanks in advance
Chris



[This message has been edited by chrismcbride (edited 05-16-2001).]

 

[kaspi]

Hi Chris
I would try to explicitly ask for values from your form controls. E.g.
Me.cboCustomer.Value
Also I would make absolutly sure that the form controls do not have same name as the recordset field e.g.
Me.inv_no - is name of the control not the name of the field (if inv_no is the name of the field and also name fo the control your code is not likely to compile).
If you still have problems, next step would be to chack the data types - and converting them to the types of your recordset e.g.
if inv_no is long integer you would put
Clng(me.inv_no.value).
Hope it helps
Kaspi

 

[Jon Holmes]

Chris,
Is Me.invExchange the name of a control on the form or is it the name of an underlying field. When you first create a form, any field in the underlying recordset can be referred to without it being tied to a control. However, if you added a new field to the recordset (by editing the underlying table or query) then although it will appear in your code options when you type Me. it won't compile. The solution is to drag the field from the field list and create a control. You can then delete this control and the module will compile. This problem also arises when you import a form into another database (along with its recordset). Any code that references fields not attached to controls will fail. The best solution is to have invisible controls.

Jon

 

[Rich]

What is this part & Forms!frmConfig_Payable_Invoice!invExchange _
& ", 0, -1, and I can't see Me.invExchange in your posted code your statement shows inv_Exchange not invExchange, I found it much easier to remove spaces in field names through out the database.
HTH

 

[chrismcbride]

Kaspi
Will try .value and changing the field names although in every case throughtout my database the field name is the same as the control name (and I have compliled before without running into this problem). Perhaps there is a data type mis-match, I will check.

Jon
Will try your solution, seems like the type of thing one can expect from MS.

Rich
Sorry about the confusion, I was grasping at straws by changing the various ways I refereced the form control value and I guess I put the wrong value in when I posted the code. Interestingly when I changed me.invExchange to Forms!frmConfig_Payable_Invoice!invExchange the compiler stopped tripping on 'Me.invExchange' and started tripping on the '&' symbol that somes right before it. Odd, eh.
Anyway many thanks to all and I will try some of this stuff out forthwith...
Chris

 

[Rich]

You might find it easier to remove the ampersands and all but first and last quotes and try one continuous line.

 

[chrismcbride]

Rich
I do not think that is the issue as I have many SQL strings that all use the format of my example and most of them will compile with no problem.