Adding values to a table using VBA.

[Christopherusly]

I have a table called tbl_esttime, what i want to be able to do is that on the click event of a button - write values to the table, so my table structure is:

tbl_esttime

ID
Job NO
Staff Member
Hours

What i need to do is write 5 new records in the table, as such:

Job NO Staff Member Hours
CO00507230 Principal Engineer 0
CO00507230 Project Manager 0
CO00507230 Senior Engineer 0
CO00507230 Senior Technician 0
CO00507230 Technician 0
CO00507230 Engineer 0

the job no will change depending upon the job being worked on, but i guess this can be retrived okay from a stored field on a form for example.

Any help or suggestions would be most welcome, thanks you guys

 

[vbaInet]

You can:

1. Create an APPEND query and EXECUTE it with vba (docmd.openquery "queryname" and close it)

OR:

2. Use an sql INSERT statement and Docmd.RUNSQL

http://www.techonthenet.com/sql/insert.php

 

[Christopherusly]

so if i use the SQL Insert which would give me something like:

INSERT INTO tbl_esttime
(Staff staffmember, Hours)
VALUES
(Technician, 0);

How would i be able to reference the job number ? my plan was to have a button on a form which say ... on load write values to table esttime, from what i can see about the SQL insert, its okay if you know what the values are going to be ... or am i being really dense on this one ?

thanks vbaInet.

 

[vbaInet]

... on load write values to table esttime

Not a good idea.

You give the INSERT statement the Job No as well.

 

[Christopherusly]

My reason for asking vbaInet is that the job no will vary depending upon the project that i am working on at the time ... so this will not be a fixed code.

 

[vbaInet]

Have a look at this example:

set db = currentdb

db.execute "INSERT INTO tbl_esttime (Staff staffmember, Hours) VALUES ([COLOR=Red][B]'[/B][/COLOR]" & txtStaff.value & "[COLOR=Red][B]'[/B][/COLOR], " & txtHours.value & ");"

Notice how staff and hours are concatenated so you can do the same for job no. Where txthours and txtstaff are textboxes (for example).

 

[Christopherusly]

By my reckoning, this should also work, if put on a button ...

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_esttime")
rs.AddNew
rs("Job No") = =[Forms]![frm_jobsetup1]![Project Code]
rs("Staff Member") = "Project Manager"
rs("Hours") = 0
rs.Update
rs.Close

also, why a bad idea to have on a form load event ? i do not follow the line of thinking ...

 

[pbaldy]

To throw out an alternative, if you have the 6 positions in a table, this would add all 6 at once:

INSERT INTO...
SELECT Postition, Forms![frm_jobsetup1]![Project Code], 0
FROM PositionTable

 

[vbaInet]

You can do, but an insert action via sql is faster than using a recordset.

It may lead to redundancy. It depends how you handle it and what it's for. It's not a completely bad idea if the record your inserting is what will be displayed on the form for scrutiny. A button to add a new record on the form after it opens is preferrable.