SQL Update Statement problem (Access 2k Win 2k)

Topic

New Reply

Dear All

Hi, I’m trying to update a check box to True with an SQL statement in VBA. The code is below. Now when I use breakpoints the code shows it has picked up on the job_number text box on the fom, however the update of the check box doesn’t happen. The field job_number field is an autonumber number field set to long integer. I’ve persuaded the code to work by typing a job in directly, but it doesn’t seem to want to ‘pick up’ on the field if it’s offerred in the me.job_number format.

In the code I’ve got ‘-1’ for the True value, this is the third alternative, I’ve already tried True and ‘True’. I’ve used -1 successfull in code before, but only to refer to a check box on a form.

Private Sub JobNotFinishedBtn_Click()
On Error GoTo ErrHandler

Dim dBs As DAO.Database

Set dBs = CurrentDb

If IsNull(Me.BreakdownListCbo) Or IsNull(Me.True_Cause) Or IsNull(Me.RepairCarriedOutByCbo) Or IsNull(Me.Number_of_People_on_Job) Then
MsgBox “Please enter all the required Data”, vbOKOnly, “Data missing error”
DoCmd.GoToControl “BreakdownListCbo”
Else
DoCmd.RunCommand acCmdSaveRecord
dBs.Execute “UPDATE [Operator breakdown entry record] SET [New Job?] = ‘-1’ WHERE [Job Number] = ” & Me.Job_Number
DoCmd.RunCommand acCmdRecordsGoToNew
End If

ExitSub:
Set dBs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Number & ” ” & Err.Description
Resume ExitSub

End Sub

This is the first time I’ve trid to use the UPDATE statement in VBA and have just copied the relevant bits from the help file, I’m guessing that some where between the help file version and mine I’ve missd something, yet again..

Thanks

Ian

Reply | Quote

Replies

Hi Ian,

Are you trying to set the value of a check box on a form or in the underlying table/recordset? It looks like you’re trying to set the value of a field in a table if certain conditions are met. You are also saving the record, if I read correctly, when those conditions are met.

Have you tried removing the single quotation mark from either side of the -1? Using the quotation marks renders what’s in between them as text.

Also, as a workaround, can you set the default value of the field in the underlying table to true? Then if conditions are not met, you don’t add/save the record..

Just a couple of thoughts.

Reply | Quote

OK,

Here’s a little more informatiom, should have covered this in the original….. Sorry.

The table I’m trying to update is not related to the form I’m running the code from, if it was I’d just use a “me.checkboxname = true” line.

The table is used to store work requests made to our maintenance team, when they have carried out work on the table the database takes the entry (via a combobox) and puts it into a second table, along with the information entered about the work they carried out. This allows the database to have many jobs entered for a single works request.

The save record instruction is for the new entry into the ‘many’ table, until this is done the data that is visible in the text box is not picked up on by the code.

What this will do is flag that the system is expecting more then one entry.

I tried taking the ‘ ‘ out from the -1 and still no change in the datatable.

To review that, I’m trying to update the one side of a relationship, while making the first related entry into the many side of the relationship.

Thanks for the reply, hope I’ve answered your questions fully.

Reply | Quote

You write “making the first related entry into the many side of the relationship”. Does that mean that there is no record on the ‘many’ side yet? If there is no record, there is nothing to update. So maybe you need an append query (INSERT INTO) instead of an update query.

Reply | Quote

Sorry, I’ve not been clear. I’m updating the existing record in the one table at the same as I create the first record in the many table. The SQL ‘UPDATE’ statement is referring to an existing record (the one side), the save command is for the new record on the many side.

Apologies for not being clear about that.

Ian

Reply | Quote

Is Job Number really a number field?

Reply | Quote

Yes, it’s an autonumber field set to long integer.

I’ve been having a look back at other things I’ve done on various Access projects as this has started to ring a little bell in my brain. I’ve used SQL strings many times before, but every single one is with a text item for the WHERE, I’ve got notes in a couple of the books I keep for each project with comments about not having been able to use numeric fields in SQL through VBA. I can work around this by creating a query and replacing my SQL string with the commands to run a query, but I would really rather like to understand why I’m having problems with SQL, VBA and numeric fields…

Ian

Reply | Quote

There is no intrinsic problem with SQL, VBA and numeric fields – in fact, numeric fields are always the easiest to handle. Try this: change

dBs.Execute “UPDATE [Operator breakdown entry record] SET [New Job?] = -1 WHERE [Job Number] = ” & Me.Job_Number

to

Dim strSQL As String
strSQL = “UPDATE [Operator breakdown entry record] SET [New Job?] = True WHERE [Job Number] = ” & Me.Job_Number
Debug.Print strSQL
DoCmd.RunSQL strSQL

In the first place, the actual SQL used will be printed to the immediate window for further inspection; you could copy and paste it into the SQL view of a new query, or you copy and paste it into a post.
In the second place, using DoCmd.RunSQL instead of dbs.Execute will make Access display the usual warnings about running an action query and updating records. How many records to be updated does it report?

Reply | Quote

There is no intrinsic problem with SQL, VBA and numeric fields – in fact, numeric fields are always the easiest to handle. Try this: change

dBs.Execute “UPDATE [Operator breakdown entry record] SET [New Job?] = -1 WHERE [Job Number] = ” & Me.Job_Number

to

Dim strSQL As String
strSQL = “UPDATE [Operator breakdown entry record] SET [New Job?] = True WHERE [Job Number] = ” & Me.Job_Number
Debug.Print strSQL
DoCmd.RunSQL strSQL

In the first place, the actual SQL used will be printed to the immediate window for further inspection; you could copy and paste it into the SQL view of a new query, or you copy and paste it into a post.
In the second place, using DoCmd.RunSQL instead of dbs.Execute will make Access display the usual warnings about running an action query and updating records. How many records to be updated does it report?

Reply | Quote

Yes, it’s an autonumber field set to long integer.

I’ve been having a look back at other things I’ve done on various Access projects as this has started to ring a little bell in my brain. I’ve used SQL strings many times before, but every single one is with a text item for the WHERE, I’ve got notes in a couple of the books I keep for each project with comments about not having been able to use numeric fields in SQL through VBA. I can work around this by creating a query and replacing my SQL string with the commands to run a query, but I would really rather like to understand why I’m having problems with SQL, VBA and numeric fields…

Ian

Reply | Quote

Is Job Number really a number field?

Reply | Quote

Sorry, I’ve not been clear. I’m updating the existing record in the one table at the same as I create the first record in the many table. The SQL ‘UPDATE’ statement is referring to an existing record (the one side), the save command is for the new record on the many side.

Apologies for not being clear about that.

Ian

Reply | Quote

You write “making the first related entry into the many side of the relationship”. Does that mean that there is no record on the ‘many’ side yet? If there is no record, there is nothing to update. So maybe you need an append query (INSERT INTO) instead of an update query.

Reply | Quote

OK,

Here’s a little more informatiom, should have covered this in the original….. Sorry.

The table I’m trying to update is not related to the form I’m running the code from, if it was I’d just use a “me.checkboxname = true” line.

The table is used to store work requests made to our maintenance team, when they have carried out work on the table the database takes the entry (via a combobox) and puts it into a second table, along with the information entered about the work they carried out. This allows the database to have many jobs entered for a single works request.

The save record instruction is for the new entry into the ‘many’ table, until this is done the data that is visible in the text box is not picked up on by the code.

What this will do is flag that the system is expecting more then one entry.

I tried taking the ‘ ‘ out from the -1 and still no change in the datatable.

To review that, I’m trying to update the one side of a relationship, while making the first related entry into the many side of the relationship.

Thanks for the reply, hope I’ve answered your questions fully.

Reply | Quote

Hi Ian,

Are you trying to set the value of a check box on a form or in the underlying table/recordset? It looks like you’re trying to set the value of a field in a table if certain conditions are met. You are also saving the record, if I read correctly, when those conditions are met.

Have you tried removing the single quotation mark from either side of the -1? Using the quotation marks renders what’s in between them as text.

Also, as a workaround, can you set the default value of the field in the underlying table to true? Then if conditions are not met, you don’t add/save the record..

Just a couple of thoughts.

Reply | Quote