Trouble with Dynamically updating Combo Boxes

[mtengelh]

Thanks John...I got a data type mismatch error when I added the second command replacing the DoCmd. Not sure why that would be?

[June7]

If Time and Date fields are Date/Time data type, parameters must be delimited with # not '.

Should not use reserved words as names.

If UserID is a number type, don't use any delimiters.

[mtengelh]

Thank you for the comment I will definitely try the suggestion but this database was working previously as it was prior to the change to the combo boxes. I only added the combo boxes that tied the two fields together and the data will no longer populate in my table.

[John_G]

In your TaskTimings table, what is the data type of [TaskType]? If it is numeric, then as June7 points out, do not use single-quote delimiters around
ComboTaskName.column(0).

For the date, format and delimit it as shown, and you are left with:

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values (" & ComboTaskName.column(0) & ",'" & User & "','" & Status & "','" & Time & "','" & Comment & "',#" & Format(Date,"mm/dd/yyyy") & "#)"

This assumes your field called [date] is date/time in your table, and I formated the date to ensure that it is in the format (US) that Access expects.

BTW, as June7 also points out, you should not name table or form fields as "Date" or "Time". Although it does often work, especially if you use [] around the names, these should be considered reserved words and avoided as object and field names.

[mtengelh]

Thank you both for the assistance and suggestions. I made the suggested changes (see below). I still am receiving a syntax error (missing operator) in query expression.

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values (" & ComboTaskName.Column(1) & ",'" & User & "','" & Status & "','" & Time & "','" & Comment & "','#" & Format(Date, "mm/dd/yyyy") & "#')"
CurrentDb.Execute State, dbFailOnError

[June7]

Remove the ' that precedes and follows #. Look closely at John's example.

Also, if [Time] is a date/time type field, replace its ' delimiters with #.

[mtengelh]

Thanks June...I removed both ' and still get the same message. [Time] is a text field

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values (" & Combo8.Column(1) & ",'" & User & "','" & Status & "','" & Time & "','" & Comment & "',#" & Format(Date, "mm/dd/yyyy") & "#)"
CurrentDb.Execute State, dbFailOnError

[June7]

Try:

Me.Combo8.Column(1)
Me.User
Me.Status
Time()
Me.Comment
Date()

[mtengelh]

Like this?

State = "INSERT INTO TaskTimings ([TaskType],[UserID],[TaskSubType],[Time],[Comment],[Date]) values (" & Me.ComboTaskName.Column(1) & ",'" & Me.User & "','" & Me.Status & "','" & Time() & "','" & Me.Comment & "',#" & Date() & "#)"
CurrentDb.Execute State, dbFailOnError

[June7]

Did it work?

Why is [Time] a text field?

Can still use the Format() function with Date() if the date structure is a concern. Review http://allenbrowne.com/ser-36.html

[mtengelh]

The change did not work. I now get an error stating Compile Error : Method or data member not found

[June7]

Are those the names of the controls as well as the fields? Could try ! instead of dot or use the actual names of the controls.

Me!User


If you want to provide db for analysis, follow instructions at bottom of my post.

[mtengelh]

TimerNewv4.zip

Attached is a copy for review.

Thanks for the assistance

[June7]

Which form? Both of them have code for the INSERT action, but only one incorporates the suggested edits - it is the Timer form.

The form is UNBOUND and the controls are UNBOUND. There are no fields to reference and no controls named ComboTaskName, User, Status. The names are Combo8, UserID, Combo20. Fix code or fix names.