Solved

Parameter Value when running code

Posted on 2008-06-18
12
184 Views
Last Modified: 2013-11-28
I am asked to enter a parameter value when I run this code. Anybody know why.

Thanks
Private Sub btn_Update_Days_Worked_Click()

 

   Dim WeekNumber As Byte

   Dim DayOfWeek As Byte

   Dim WeekText(1 To 2) As String

   Dim CurrentCheckBox As CheckBox

   

   For WeekNumber = 1 To 2

      For DayOfWeek = 1 To 7

         Set CurrentCheckBox = Me("WKDay" & LeadingZero(DayOfWeek + ((WeekNumber - 1) * 7)))

         If CurrentCheckBox = 0 Then

            WeekText(WeekNumber) = WeekText(WeekNumber) & "X"

         Else

            WeekText(WeekNumber) = WeekText(WeekNumber) & GetFirstLetterOfDay(DayOfWeek)

         End If

      Next

   Next

   DoCmd.RunSQL "INSERT INTO tbl_EmployeeShiftInformation (DaysWorkedWK1, DaysWorkedWK2) VALUES ([WeekText(1)], [WeekText(2)])"

   

End Sub

Open in new window

0
Comment
Question by:life42
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 3

Expert Comment

by:BitRunner303
ID: 21812925
See if this works:

DoCmd.RunSQL "INSERT INTO tbl_EmployeeShiftInformation (DaysWorkedWK1, DaysWorkedWK2) VALUES (""" & WeekText(1) & """, """ & WeekText(2) & """)"
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21812945
the error is this line:
 DoCmd.RunSQL "INSERT INTO tbl_EmployeeShiftInformation (DaysWorkedWK1, DaysWorkedWK2) VALUES ([WeekText(1)], [WeekText(2)])"

it should be:
 DoCmd.RunSQL "INSERT INTO tbl_EmployeeShiftInformation (DaysWorkedWK1, DaysWorkedWK2) VALUES ('" & WeekText(1) & "','" & WeekText(2) & "')"
0
 

Author Comment

by:life42
ID: 21813030
I now get the following message: Microsoft Office can't append all the records in the append query.

I just want to update a single record in the table.
0
 
LVL 19

Expert Comment

by:frankytee
ID: 21813137
thats because your sql statement is an insert (ie append) and not an update statement
an update statement would be something like:
DoCmd.RunSQL "update tbl_EmployeeShiftInformation set DaysWorkedWK1 ='" & WeekText(1) & ', DaysWorkedWK2 = '" & WeekText(2) & "'" & " WHERE ..... whatever record id etc"


0
 
LVL 19

Accepted Solution

by:
frankytee earned 250 total points
ID: 21813152
typo, it should be (replace accordingly):
DoCmd.RunSQL "update tbl_EmployeeShiftInformation set DaysWorkedWK1 ='" & WeekText(1) & "', DaysWorkedWK2 = '" & WeekText(2) & "'" & " WHERE whateverIDfield = " & whateverid
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21813162
you will need an update query not an insert (append) query

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:life42
ID: 21813163
frankytee, I will try this, but low group question , why an update not an insert statement.

Thank you
0
 

Author Comment

by:life42
ID: 21813443
What do I do if I am populating a new table with empty fields how do I write this UPDATE statement?
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21813665
life42,

updating a field in a table means revising/editing the fields value of an existing record.

appending to a table means inserting/adding a new record to the table.


what do you want to do?
0
 

Author Comment

by:life42
ID: 21813704
I want to update a table, that has employee id employee name and department already populated I want to add what days of the week they work based on checkbox values. I have the checkbox values stored, I just need to update the employee record in the table based on the input from the form.

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 21813899
frankytee already posted the codes at http:#a21813152
0
 

Author Closing Comment

by:life42
ID: 31468326
Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now