Need to Insert fields from form into table

[Avaldez21]

I have a form that has users name, project number, date, task, drawing number, revision number, hours works, and work done. I was trying to figure out a way to put these fields in my table called work done when the submit button is clicked. Also, after it puts this information into the table I would like it to clear out the form so they can enter any other work done that day. I was interesting in how to get the code started for this. I tried searching the forum but did not come up with anything. This is my first project in Access but I am fairly familiar to VB code. Any point in the right direction would be greatly appreciated.

Thanks,
Aaron

 

[namliam]

Are you sure you want to make an Unbound form when you are just starting in Access??

If you are familiar to VB, than you should know how to work with forms and tables without problems. VBA works much the same as VB, so you should atleast be able to get 90% there without any help...

 

[Avaldez21]

I do know how to work with forms and tables. This is the last step of the project, I just needed a little help in the right direction to get this information put into the table and clear the form. I already made the unbound form and it works perfectly except for my submit button haha. Any suggestions?

 

[Avaldez21]

Would I use the insert into command?

 

[gemma-the-husky]

you need to execute asql command that looks like

insert into mytable (field1, field2, field3) select value1, value2, value3

but building this wil be quite complex

-----------
then you need to reset all your fields as blanks/nils with code

---------
but you may not need to do this

just have a bound form with entryform set to true - then you wont see any existing records, you can just use this to add new records

 

[Avaldez21]

Thank you I appreciate your help. I will try it.

 

[Avaldez21]

When I wrote the code it comes up with a Compile Error saying Expected: end of statement with my table name highlighted.

 

[gemma-the-husky]

the string you are forming needs to be precisely formatted - can you post what you have got

 

[Avaldez21]

This is what I typed up

Code:
INSERT INTO WorkDone (ProjectNum, DrawingNumber, Revision, Name, DateofWork,_
HoursWorked, Task, WorkDescription) select ProjectNumTxt,_
DwgNum, RevNum, NameTxt, Date,_
HrsWorkedtxt, TaskTxt, WorkDoneTxt

 

[namliam]

Or you can do it like in VB with a recordset reading stuff of your form much like you do in VB.

 

[gemma-the-husky]

sqlstrg = "
INSERT INTO WorkDone (ProjectNum, DrawingNumber, Revision, Name, DateofWork,_
HoursWorked, Task, WorkDescription) select "

you are ok so far, but now you have to wrap everything properly

for numbers, you can just use the number
for text, everything has to be surrounded by a "" character
for dates, everything has to be surrounded by a # character

you also have to be aware of nulls/blanks etc, if your table wont accept zls values

its not easy

---------
so even if erything is numeric you still HAVE to now say

& ProjectNumTxt & " , " & DwgNum & " , " etc
making sure to leave spaces between everything

----------
Can you really not use a bound form!

 

[Avaldez21]

Well I'm having trouble with the code, not to surprised. If I can use this as a bound form I'm not sure how. First I have a project number pulled from a query in a combo, then I have a cascading combo to pull out the project team which ever project is selected. They select their name from a combo and then choose a task they have completed from a task query, there's another cascading combo to show them the description of the task. If they worked on a specific drawing this is their next choice. I created another cascading combo to bring up appropriate drawings based on the project number. Once they select a drawing there is another cascading combo to bring up the corresponding revision number, drawing name, hours allowed for that drawing, and hours that have been worked on that drawing. They then have to input how many hours they've worked on this drawing and input what they did. I came into this project not knowing anything about access or how it worked so I did not know the difference of bound forms and unbound forms until I started this thread.