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
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
methd is a string.
change end of Sq string to:
newamount & ", '" & methd & "' );"
Need delimiters for date also:
& CONSID & " , " & FKCNFID & " , #" & dt & "# , " & newAmount & " , '" & methd & "' );"
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
Last edited by davegri; 11-29-2020 at 11:12 AM. Reason: clarif
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.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
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.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?
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.
Use a format for the correct storage.
I use a constant as I can never remember the correct syntax.
Code:Public Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Needed for dates in queries as Access expects USA format.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
You use it to format the date.?
Lookup the Format() function.
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
# delimiter was there.
But in case of using the constant do I just change it's declaration as below?Code:" , #" & Format(dt, "yyyy-mm-dd") & "# , "
any other change?Code:Const dt = Me.Paid_Dt