Update table from form combo

[Gaetanm]

I’m new at this and trying very hard. Can anyone help?
My table that needs to be updated is Clock_Table
The table has four (4) fields JobId, EmployeeID, StartDate, StopDate

My form called StopTime has a combo box bound to this table.
The combo does a query EmployeeID that StartDate is not Null and StopDate
Is Null. The combo box display the EmployeeId. I have a field on the Form
that is populated via a button that has the Now statement. That part works.

I’m trying to update the existing row to the table with the Employee value in
the combo box as point of reference and update StopDate in the table with the
StopDate data
that was put on the form via the Now statement.

I tried with the following code via a command button but its not working It
comes up with a dialogue box asking to put the variable in then another
dialogue box pops telling me 0 records will be updated .

Private Sub Command21_Click()

Dim SQLstrg As String
SQLstrg = "Update Clock_Table Set EmployeeId = [EmployeeId].[Column(1)],
StopDate = [StopDate] Where StopDate = Null"
DoCmd.RunSQL SQLstrg
End Sub

Can anyone please help

 

[kingston via AccessMonster.com]

It's not clear whether you want to add a new record because you try to set
the EmployeeID in your example. However, from the rest of your description,
it sounds like you just want to update an existing record. Anyway, I'm not
sure if your post got cut-off or whether your code is incorrect, but try this
to update the table:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# WHERE ((
[StopDate] Is Null) AND ([EmployeeID] = " & Me.[EmployeeId].[Column(1)] & "))
;"

I’m new at this and trying very hard. Can anyone help?
My table that needs to be updated is Clock_Table
The table has four (4) fields JobId, EmployeeID, StartDate, StopDate

My form called StopTime has a combo box bound to this table.
The combo does a query EmployeeID that StartDate is not Null and StopDate
Is Null. The combo box display the EmployeeId. I have a field on the Form
that is populated via a button that has the Now statement. That part works.

I’m trying to update the existing row to the table with the Employee value in
the combo box as point of reference and update StopDate in the table with the
StopDate data
that was put on the form via the Now statement.

I tried with the following code via a command button but its not working It
comes up with a dialogue box asking to put the variable in then another
dialogue box pops telling me 0 records will be updated .

Private Sub Command21_Click()

Dim SQLstrg As String
SQLstrg = "Update Clock_Table Set EmployeeId = [EmployeeId].[Column(1)],
StopDate = [StopDate] Where StopDate = Null"
DoCmd.RunSQL SQLstrg
End Sub

Can anyone please help

 

[Gaetanm via AccessMonster.com]

It's not clear whether you want to add a new record because you try to set
the EmployeeID in your example. However, from the rest of your description,
it sounds like you just want to update an existing record. Anyway, I'm not
sure if your post got cut-off or whether your code is incorrect, but try this
to update the table:

SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# WHERE ((
[StopDate] Is Null) AND ([EmployeeID] = " & Me.[EmployeeId].[Column(1)] & "))
;"

I’m new at this and trying very hard. Can anyone help?
My table that needs to be updated is Clock_Table

[quoted text clipped - 23 lines]

Can anyone please help

Kingston

Thanks for the quick reply but I'm still having a problem as indicated below.

Any assistance would be appreciated


Private Sub Command21_Click()

Dim SQLstrg As String
SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# WHERE (
("
[StopDate] Is Null) AND ([EmployeeID] = " & Me.[EmployeeId].[Column(1)] & "))
;

End Sub

It comes back with compiler syntax error , and then the data base is locked
I have to shut it down without saving

Gaetanm

 

[kingston via AccessMonster.com]

It looks like there is a misplaced " in the WHERE portion of the SQL
statement. Before you run a procedure, compile it first to look for syntax
errors (Debug menu).

It's not clear whether you want to add a new record because you try to set
the EmployeeID in your example. However, from the rest of your description,

[quoted text clipped - 11 lines]
Kingston

Thanks for the quick reply but I'm still having a problem as indicated below.

Any assistance would be appreciated

Private Sub Command21_Click()

Dim SQLstrg As String
SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# WHERE (
("
[StopDate] Is Null) AND ([EmployeeID] = " & Me.[EmployeeId].[Column(1)] & "))
;

End Sub

It comes back with compiler syntax error , and then the data base is locked
I have to shut it down without saving

Gaetanm

 

[Gaetanm via AccessMonster.com]

It looks like there is a misplaced " in the WHERE portion of the SQL
statement. Before you run a procedure, compile it first to look for syntax
errors (Debug menu).
[quoted text clipped - 22 lines]

Gaetanm

Kingston
Thanks for reminding me about Debug. In the code (( Where
Access appears to put it in as (("Where

I Delete it it comes back. I even pasted your code in and Access added the "
Any Ideas why

Gaetanm

 

[Gaetanm via AccessMonster.com]

It looks like there is a misplaced " in the WHERE portion of the SQL
statement. Before you run a procedure, compile it first to look for syntax

[quoted text clipped - 5 lines]
Kingston
Thanks for reminding me about Debug. In the code (( Where
Access appears to put it in as (("Where

I Delete it it comes back. I even pasted your code in and Access added the "
Any Ideas why

Gaetanm

Kingston

Sorry on the previous post that should have been
WHERE ((" and the " is being added after the WHERE ((

Gaetanm

 

[kingston via AccessMonster.com]

You have to correct the format of the SQL string because this forum reformats
my messages with line breaks. The string I provided is one long line. If
you want to break it up in VBA, use & _ to continue a string onto the next
line. If Access breaks it up, it will try to add quotation marks and
generally gets the location wrong.

[quoted text clipped - 10 lines]

Gaetanm

Kingston

Sorry on the previous post that should have been
WHERE ((" and the " is being added after the WHERE ((

Gaetanm

 

[Gaetanm via AccessMonster.com]

You have to correct the format of the SQL string because this forum reformats
my messages with line breaks. The string I provided is one long line. If
you want to break it up in VBA, use & _ to continue a string onto the next
line. If Access breaks it up, it will try to add quotation marks and
generally gets the location wrong.
[quoted text clipped - 7 lines]

Gaetanm

Kingston

Thanks for that info on the lines being broken up and how to fix it.

I tried to compile and got Sub or Funtion Not Defined

The Code follows

Dim SQLstrg As String
SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# &_"
WHERE (([StopDate] Is Null) And ([EmployeeID] = " & Me.[EmployeeId].[Column(1)
] & "))
;"

Is the problem it does not know what to do with And ([EmployeeID]=

Gaetanm

 

[kingston via AccessMonster.com]

In order to continue a string, add this at the end of the broken line: & _.
Do not add a quotation mark after it. However, you don't need a line break;
just put my original code in one long line.

You have to correct the format of the SQL string because this forum reformats
my messages with line breaks. The string I provided is one long line. If

[quoted text clipped - 7 lines]
Kingston

Thanks for that info on the lines being broken up and how to fix it.

I tried to compile and got Sub or Funtion Not Defined

The Code follows

Dim SQLstrg As String
SQLstrg = "UPDATE Clock_Table SET [StopDate] = #" & Me.StopDate & "# &_"
WHERE (([StopDate] Is Null) And ([EmployeeID] = " & Me.[EmployeeId].[Column(1)
] & "))
;"

Is the problem it does not know what to do with And ([EmployeeID]=

Gaetanm

 

[Gaetanm via AccessMonster.com]

In order to continue a string, add this at the end of the broken line: & _.
Do not add a quotation mark after it. However, you don't need a line break;
just put my original code in one long line.
[quoted text clipped - 19 lines]

Gaetanm

Thanks for your patience I did what you suggested and now I receieved
a Run-time error 438 Object Doesn't support this property or method

I have looked at other post about this error but I don't to be able to see
the solution

Gaetan