Insert into query vba

[jaryszek]

I have a form where recordsource is from 2 tables:





as you can see my summary fact table is tbl_Braki - it has Employee_ID_FK which is foreign key in one-to-many relationship with table tbl_Employee_SAP.

Now i am using button to insert into tbl_Braki table new records.
My form is based on comboboxes: first one in the picture (with Employee_ID from tbl_Employees_SAP table) which indicate which ForeignID should take and insert it into tbl_Braki.



Now i would like to input data into Podobszar_Pr field (text) in tbl_Employees_SAP (not in tbl_Braki!) from my form.

When i am trying to insert data into tbl_Braki :

 SqlString = "INSERT INTO tbl_Braki([Numer_zgłoszenia], [Zleceniodawca_nr_FK], [Document_ID_FK], [Employee_ID_FK], [Data_Wysłania], [Data_Zwrotu], [Uwagi], [Login], [Podobszar_Pr]) " & _
                    "VALUES ('" & NewOne & "', '" & ComboCompanyID & "', '" & DokumentID & "', '" & ComboSapID & "', '" & Data_Wysłania.Value & "', '" & Data_Zwrotu.Value & "', '" & Uwagi.Value & "', '" & LoginName & "','Wroclaw');"

there is an error because [Podobszar_Pr] is not exists in tbl_Braki table, it is existing only in tbl_Employees_SAP table !


Only solution which i know is creating SQL command twice.

So i can first insert into tbl_Braki field Employee_ID_FK and having this ID i can run second SQL command which will be inserting Podobszar into tbl_Employees_SAP table (based on inputed Employee_ID_FK)

But i know that in Access has to be better way to do it,
please help me,
Best Wishes,
Jacek Antek

[jaryszek]

Okey,

i have noticed that insert into query is not working with VALUES statement.

So my code is working now:

SqlString = "UPDATE tbl_Employees_SAP" & _" SET [Podobszar_Pr] = '" & ComboPodobszarID & "'" & _
" WHERE Employee_ID= " & ComboSapID & ";"
    
DoCmd.RunSQL SqlString

but with UPDATE statement.

Problem is that user should write only one new Podobszar_pr field, not updating it all the time.

For example:

User will choose Employee John Smith with ID 1001.
Will Choose Warsaw as Podobszar_pr.

After starting button macro Id 1001 in tbl_Employees_SAP table will be updated with value Warsaw.

And new user should not have possibility to update this filed with the same value (Warsaw).
User should have possibility eventually to change it.

I can do it via subform but I am not convinced if subform only for one form it is a good choice...

Your tips will be welcome,
Thank you in advance,
Jacek Antek

[ssanfu]

I can do it via subform but I am not convinced if subform only for one form it is a good choice...

The general rule for adding/editing data to forms is one table-one form. I use a main form/sub form all of the time.


You can change "Podobszar_Pr" by using an unbound combo box and using VBA to execute the update.
But I don't understand why you are using an append query (INSERT INTO...) to add the data if the form is bound to the table "tbl_Braki".

[orange]

Jacek,

You only need quote delimiters for text datatype. Is ComboPodobszarID text or numeric??
Can you tell us more about
"Problem is that user should write only one new Podobszar_pr field, not updating it all the time."

Your SQL will
Update every record where Employee_ID= ComboSapID

Also, this seems to be a logic/control issue for your design
And new user should not have possibility to update this filed with the same value (Warsaw).

Don't make the option available to a new user, if that is a business rule.

[jaryszek]

Hi ssanfu, Hi orange

Thank you very very much !

But I don't understand why you are using an append query (INSERT INTO...) to add the data if the form is bound to the table "tbl_Braki".

When you wrote this - now i know that i don't have to always write VBA code and use Access built-in functions... thank you!

Is ComboPodobszarID text or numeric??

It is a text field.

Now my comboboxes using SQL update statement after afterUpdate event.

So all is working fine !

I am closing the topic,

Best wishes,
Jacek Antek