hardcore coders only...

[1jet]

Hey all...

Please have a quick look at my form first...

By f22a at 2008-09-25

I've worked up some code as below...

Function vbaTestAppend()

Dim frm As Form
Dim project_count As Integer
Dim day_count As Integer

Set frm = Forms!frmEmployeeTimesheet
project_count = 0
day_count = 0

Do
    project_count = project_count + 1
    day_count = 0
        Do
            day_count = day_count + 1
            MsgBox "INSERT INTO tblHours ( [Project ID], [Employee ID], Date, Hours ) SELECT Forms!frmEmployeeTimesheet!cboSelectProject" & project_count & ", Forms!frmEmployeeTimesheet!cboSelectName, Forms!frmEmployeeTimesheet!txtDay" & day_count & ", Forms!frmEmployeeTimesheet!txtProj1Day" & day_count & ";"
        Loop Until day_count = 7
Loop Until project_count = 5

End Function

Now as I'm new to VBA and Access forms, I'm sure my approach will be frowned upon by alot of you.

But you can see what I'm trying to do right?
Hopefully with your help I'll be able to convert the query in the message box into a "RunSQL" (I think) and have the loop stop when a cboSelectProject is empty.

If my code has started off on the wrong foot, I'm open to new ideas.

Thanks!

 

[Rabbie]

Firstly you shouldn't have names like Date in your table as Date is a reserved word in Access. This will give you problems.

At present you are assuming there is valid data in all your input fields. Not necessarily true. a person may not have worked on 5 projects and may not have worked on all seven days. You need to cater for that to avoid a lot of zero data.

 

[Guus2005]

Apart from what Rabbie suggests:

When you change the msgbox to RunSQL you wil get a lot of errors because the quotes do not make a lot of sense.

Change the SELECT into a VALUES and start from there.

 

[1jet]

I've changed SELECT to VALUES, but still get the "UPDATE INTO syntax error" message from access when I try execute it.

I've changed the code by introducing two more string variables to clean up the query a bit...then had it output to a msgbox again and the query looks perfect.

Function vbaTestAppend()

[COLOR=Silver]'Dim frm As Form[/COLOR]
Dim strSQL As String
Dim project_count As Integer
Dim day_count As Integer

[COLOR=Silver]' test[/COLOR]
Dim projectcbo As String
Dim datebox As String
Dim daybox As String

[COLOR=Silver]'Set frm = Forms!frmEmployeeTimesheet
'project_count originally = 0[/COLOR]
project_count = 1
day_count = 0

[COLOR=Silver]'Do
'    project_count = project_count + 1[/COLOR]
    projectcbo = "Forms!frmEmployeeTimesheet!cboSelectProject" & project_count
[COLOR=Silver]'    day_count = 0[/COLOR]
        Do
            day_count = day_count + 1
            datebox = "Forms!frmEmployeeTimesheet!txtDay" & day_count
            daybox = "Forms!frmEmployeeTimesheet!txtProj" & project_count & "Day" & day_count
            strSQL = "INSERT INTO tblHours ( [Project ID], [Employee ID], Date, Hours ) VALUES " & projectcbo & ", Forms!frmEmployeeTimesheet!cboSelectName, " & datebox & ", " & daybox & ";"
            DoCmd.RunSQL strSQL
        Loop Until day_count = 7
[COLOR=Silver]'Loop Until project_count = 5[/COLOR]

End Function

I've greyed out other coding that I may use later for the other project rows. Apologies if it's messy.

I've read some other posts, do you think I need to use single inverted commas for any of my strings?

I've no idea why the query looks fine on msgbox but won't run.

 

[Rabbie]

Step through your procedure using F8 and do a diagnostic print of strSQL just before the DoCmd.runSQL to see exactly what you are trying to run.

You still need to rename the Date field in your table because that WILL cause lots of problems. Date is the name of a function in Access and shouldn't be used for anything else.

 

[1jet]

Yeah I should change Date to something else.

What was the line for the diagnostic print?
I remember it something like...Debug.RunSQL strSQL?

 

[georgedwilkinson]

Debug.Print strSQL

 

[Rabbie]

debug.print strSQL

 

[1jet]

Rabbie my code seems fine.
The error comes from Access as an "INSERT INTO syntax error"

Here's the msgbox and query

By f22a at 2008-09-25


Here's the query being run

By f22a at 2008-09-25

I hope this can clarify things for you

 

[MSAccessRookie]

I converted your query as I believe that MS Access would, and I believe that it will be interpreted as follows:

[COLOR=black][FONT=Courier New][SIZE=3]INSERT INTO tblHours ( [Project ID], [Employee ID], Date, Hours ) [/SIZE][/FONT][/COLOR]
[FONT=Courier New][COLOR=black][SIZE=3]VALUES {projectcbo}, Forms!frmEmployeeTimesheet!cboSelectName, {datebox}, {daybox};[/SIZE][/COLOR][/FONT]

If this is true, then I think that you should consider the following changes:

[FONT=Courier New][COLOR=black][SIZE=3]INSERT INTO tblHours ( [Project ID], [Employee ID], [B][SIZE=5][COLOR=red]TS_Date[/COLOR][/SIZE][/B], [SIZE=5][B][COLOR=#ff0000]TS_[/COLOR][COLOR=red]Hours[/COLOR][/B][/SIZE] )[/SIZE][/COLOR][/FONT]
[FONT=Courier New][COLOR=black][SIZE=3]VALUES [SIZE=5][COLOR=red]([/COLOR][/SIZE]" & projectcbo & ", Forms!frmEmployeeTimesheet!cboSelectName, " & [B][SIZE=5][COLOR=red]cstr([/COLOR][/SIZE][/B]datebox[SIZE=5][COLOR=red][B])[/B][/COLOR][/SIZE] & ", " & [B][SIZE=5][COLOR=#ff0000]cstr([/COLOR][/SIZE][/B]daybox[B][SIZE=5][COLOR=#ff0000])[/COLOR][/SIZE][/B] & "[SIZE=5][COLOR=red][B])[/B][/COLOR][/SIZE];"[/SIZE][/COLOR][/FONT]

Note that the opening and closing "(" and ")" for the Values clause was left out. I think that they are required (at least I have always used them).

Second, cstr has been added in the case that the fields are stored as Date/Time format (If they are already strings you will not need this change).

Finally, as you have been previously advised, it is not a good Idea to use a column named DATE. I changed TIME to make it consistent with DATE, as well as a PRO-Active measure, because other versions of SQL have Time() functions.

 

[1jet]

Re: Note that the opening and closing "(" and ")" for the Values clause was left out.

You know this was the problem this whole damn time.
I thank your eye for detail.

I've also used the cstr as you recommended, but there is another problem.

When I try and append 3.5 hours, the table rounds it up to 4.
I've already gone into the table, checked that the TS_Hours data type is number, and set decimal points to 2.

The field size is "long integer." Would it help changing it to integer? decimal?
I don't know the differences yet.

Thanks heaps

 

[boblarson]

An integer has no decimal places (it is a WHOLE number).

 

[1jet]

changed the text boxes in the forms to be of a "general number" format
(previously it was "fixed")

and changed the TS_Hours field size in the table to "double" and that fixed the problem

cheers rookie

 

[MSAccessRookie]

changed the text boxes in the forms to be of a "general number" format
(previously it was "fixed")

and changed the TS_Hours field size in the table to "double" and that fixed the problem

cheers rookie

Sheriff Bob (One of the fastest and most qualified responders out here) beat me to it again. I was doing real work and did not read the forum quick enough. My pleasure for helping out. I have always found that the simplest explanation is the right one more often than not.

Good luck and cheers to you too!