Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Is Job Number really a number field?

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •