insert code

[krzysiekk]

Hi I got problem with insert event in access form.

I try use


DoCmd.SetWarnings False
DoCmd.RunSQL "insert into vection ([trailer], [status], [required_date]) Values ([Forms]![Form1]![trailer], 'U', [Forms]![Form1]![req_date]) where [Forms]![Form1]![req_date] not in (select distinct required_date from vection);"

showing error because I using where with Value, is any way to resolve this insert?

Please advise

 

[jzwp22]

The syntax of the query is not correct. Since you are trying to supply variable data from your form, those variables cannot be enclosed within the double quotes of the string variable. Additionally, text values must be enclosed by single quotes and dates by # signs. Further, using the INSERT...VALUE syntax of the query, you cannot have a WHERE clause.


You either have to use this syntax

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

or this

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"
WHERE...

So dropping the WHERE clause and correcting the syntax for the variables yields this (this assumes that the trailer field is numeric and the required_date field is a date/time datatype)

DoCmd.RunSQL "insert into vection ([trailer], [status], [required_date]) Values (" & [Forms]![Form1]![trailer] & ", 'U', #" & [Forms]![Form1]![req_date] & "#)"

Now, to check the date, you will have to do that when the date is entered into the form before the query is run, so I would recommend the After Update event of the date control. You could probably use a domain aggregate function to see if the date meets your requirements.

 

[krzysiekk]

Thanks you correct, you right, but is possible use the where clausle?

 

[jzwp22]

No, you cannot use the WHERE clause with the INSERT INTO VALUES syntax, so you will have to check the date using other code.

 

[krzysiekk]

How to check data using code. What I want to do is: load data from form: trailer, trailer_date, status and required date, insert date to table vection but I need chceck first to avoid duplication in table.

Somting like insert if trailer and trailer_date not exising in table.

Could you help me - I not idea how I need resolved that issue

 

[jzwp22]

Somting like insert if trailer and trailer_date not exising in table.

What do you want to do if the trailer and trailer_date already exist in the table? Do you want to update the required date?

Before you run the code to do the append, you could check to see if the record exists. This is what the code would look like in a general sense:

IF dcount("*", "vection", "trailer='" & me.trailer & "' AND trailer_date=#" & me.trailer_date & "#")>0 THEN
'a record exists with the trailer and trailer date indicated on the form
'do something
ELSE
'a record does not exist with the trailer and trailer date indicated on the form

'execute the append query here

END IF

 

[krzysiekk]

If exisitng do nothing

 

[jzwp22]

That simplifies things a little; the code would look something like this. I assumed that the trailer field is numeric and the trailer_date field is a date/time datatype.

IF dcount("*", "vection", "trailer='" & me.trailer & "' AND trailer_date=#" & me.trailer_date & "#")=0 THEN

DoCmd.RunSQL "insert into vection ([trailer], [status], [required_date]) Values (" & [Forms]![Form1]![trailer] & ", 'U', #" & [Forms]![Form1]![req_date] & "#)"


END IF

 

[krzysiekk]

showing error: Method or data member not found

 

[jzwp22]

You will have to adjust my code to match your table names, field names and form control names for the code to work successfully.

What section of the code did the error highlight?

 

[krzysiekk]

Private Sub Combo12_AfterUpdate()

 

[jzwp22]

I see that you have it in the after update event of the combo box. That is incorrect. You need to have it behind a button on the form since the code refers to more than just the combo box control.

 

[krzysiekk]

When I created button command and event on click still showing same message

 

[jzwp22]

You will have to check to make sure all of the names referenced in the code are consistent with the names of the controls on your form as well as the names of fields and tables.

Can you post your entire code or post your database (with any sensitive data removed)?

 

[krzysiekk]

The sample data in mdb

 

[jzwp22]

In your sample database the trailer_date field was text datatype, so I changed it to a date/time datatype. In my earlier example, I said that I assumed trailer was numeric, but in your database it is a text field, so that requires a little different syntax. This is why you got the code error.

You had mentioned that you wanted to check to see if a record existed that had the same trailer and trailer_date, but I don't see a control on your example for trailer_date (and it is not in the sage table), so I substituted the req_date in the DCount() function for test purposes.

I assume at some point someone will manually enter the trailer_date into the vection table otherwise the code will always run the append query.

I also changed from the docmd to an execute statement; with the execute statement, you do not need to turn warnings off before running the append query (and back on afterwards).

I also separated the SQL text to a variable that way I can use the debug.print variablename statement that will copy the SQL text to the VBA immediate window. This aids in troubleshooting the query since you can copy and paste the SQL text to a new query and test it for syntax errors.

The amended database is attached.

 

[krzysiekk]

any luck with mdb file?

 

[jzwp22]

See my earlier post; it includes the corrected database.

 

[krzysiekk]

Your check you modification and still inseritng rows even exisiting, on you machine working fine?

 

[jzwp22]

Yes, what I posted worked fine on my machine, you have to make a selection in the combo box on the form for the code to execute.