Error on insert into command

[bubai]

In my form I have created a button and added an insert into code. But on click event it crashes and shows following errors.

and



Db is as attached.
Insert.zip

[davegri]

methd is a string.
change end of Sq string to:

newamount & ", '" & methd & "' );"

[bubai]

methd is a string.
change end of Sq string to:

newamount & ", '" & methd & "' );"

Thanks mate. I works.... But on the date field it puts time instead of a date value.

[davegri]

Need delimiters for date also:
& CONSID & " , " & FKCNFID & " , #" & dt & "# , " & newAmount & " , '" & methd & "' );"

[bubai]

Need delimiters for date also:
& CONSID & " , " & FKCNFID & " , #" & dt & "# , " & newAmount & " , '" & methd & "' );"

Cheers mate!
But what if I want to insert a NULL value to the 2nd field (ie. CONSID) instead of Me.Cons_ID value?

[davegri]

Insert-davegri-v01.zip

See attached.
Had to make too many adjustments to show in this message.
Removed table validation constraints for "values >0" so that null was allowed.
Changed Dim of CONSID to variant to allow for null assignment
Added record to lookup table _cnflookup to include a null (blank) selection in the combobox dropdown.
Changed some keys from integer to long

[bubai]

Insert-davegri-v01.zip

See attached.
Had to make too many adjustments to show in this message.
Removed table validation constraints for "values >0" so that null was allowed.
Changed Dim of CONSID to variant to allow for null assignment
Added record to lookup table _cnflookup to include a null (blank) selection in the combobox dropdown.
Changed some keys from integer to long

I think there had been a misinterpretation between us. What I was looking for was to insert Null instead of CONSID, which looks like achievable by placing & "NULL" & " , " & FKCNFID & instead of CONSID in the insert statement.
However 0 in the FK_CNFID field of payment_CNF table shows blank when represented through the CNF_ID combobox. Why is so?

[davegri]

I think there had been a misinterpretation between us. What I was looking for was to insert Null instead of CONSID, which looks like achievable by placing & "NULL" & " , " & FKCNFID & instead of CONSID in the insert statement.
However 0 in the FK_CNFID field of payment_CNF table shows blank when represented through the CNF_ID combobox. Why is so?

Yes, but that requires a different SQL string, while the original is still required when CONSID is not null. In my change, the SQL string will handle both.
The blank row in CNF_ID combobox allows you to select a null value to insert as you requested in post #5.

[bubai]

Yes, but that requires a different SQL string, while the original is still required when CONSID is not null. In my change, the SQL string will handle both.
The blank row in CNF_ID combobox allows you to select a null value to insert as you requested in post #5.

Hi Dave.
I need some time to grasp your code.
But at the moment facing a weird problem. The date which is copied and the date which is saved on insert event does not remain the same. In my PC I use British date format. dd/mm/yy. When the append query runs the values of the date parts get interchanged. Hence makes it a different date.

[Welshgasman]

Use a format for the correct storage.

I use a constant as I can never remember the correct syntax.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

[bubai]

Use a format for the correct storage.

I use a constant as I can never remember the correct syntax.

Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.

How to use it?

[Welshgasman]

You use it to format the date.?

Lookup the Format() function.

[bubai]

You use it to format the date.?

Lookup the Format() function.

I have wrapped the variable of the insert sql with Format function as below.

Format(dt, "yyyy-mm-dd")

[Welshgasman]

I have wrapped the variable of the insert sql with Format function as below.

Format(dt, "yyyy-mm-dd")

You were meant to use my constant and not "yyyy-mm-dd", but if it works, leave it as it is. Normally I would use the # delimiter.

[bubai]

You were meant to use my constant and not "yyyy-mm-dd", but if it works, leave it as it is. Normally I would use the # delimiter.

# delimiter was there.

" , #" & Format(dt, "yyyy-mm-dd") & "# , "

But in case of using the constant do I just change it's declaration as below?

Const dt = Me.Paid_Dt

any other change?