Help with update statement VBA

Discussion in 'Programming & Software Development' started by LayZ, May 21, 2018.

  1. LayZ

    LayZ Member

    Joined:
    Sep 7, 2011
    Messages:
    88
    Hey guys,

    Currently undertaking a placement and have a ridiculous data entry task which involves copy & pasting data across numerous lines in a form.

    I'm trying to make a button to try speed this up and have little to no knowledge of databases... This is in Access.

    There is a table of parts which apply to a range of models, my task is assigning times to each model for a given part. Currently I must manually enter the times for each model despite 90% of parts having the same time for each model. I have added the button and here is the code I have made.

    ===============================================================================

    Private Sub btnCopyTimes_Click()

    ' Define variables
    Dim EnteredValue As Double
    Dim IDToUpdate As String
    Dim strSQL As String

    ' Grab ID on form
    IDToUpdate = Me.FINLDATA_ID.Value

    ' Prompt for new base time for ALL models
    EnteredValue = InputBox("Enter New Base Time for ALL models")

    ' Update table with new base time for ALL models with specific ID
    strSQL = UPDATE FINLDATA MODELS
    SET BASE TIME = EnteredValue
    WHERE FINLDATA MODELS.FINLDATA_ID = IDToUpdate

    RunSQL strSQL

    End Sub

    ===============================================================================

    It's not happy with the Update statement and I have no doubt I've butchered it but unsure where to start...

    If you have any questions I'll try answer.
     
  2. GTR27

    GTR27 Member

    Joined:
    Aug 16, 2002
    Messages:
    7,969
    Location:
    Sydney
    Silly question, cant you just write an SQL to update certain records with the times? If 90% are the same, update to that value, then do the other 10% manually

    Access has that easy "SQL for dummies" part where you literally click on a drop down menu to select fields in the table and you can add restrictions in each field. I think if you search "append or update multiple records" it gives you a run down on how to do this with a query.
     
  3. OP
    OP
    LayZ

    LayZ Member

    Joined:
    Sep 7, 2011
    Messages:
    88
    Unfortunately it isn't that easy due to the table design, it is easier to update using the form that someones made.

    The tables are a mess of part numbers, almost over a million whereas on the form I can search for the ~1000 I want and update accordingly. I'll look in to using the append query but I have doubts.

    Process in table would be

    Look up part number > Filter out superseded models and 'periods' e.g. JAN07, FEB07 etc. > Update 'BASE TIME' for all models.

    I am having problems wrapping my head around appending the records, is it possible to prompt input while running a query? How would it get the first time to assign to each other model
     
    Last edited: May 22, 2018
  4. akostar5

    akostar5 Member

    Joined:
    Apr 4, 2015
    Messages:
    1
    Location:
    Melbourne
    Where would you be getting your data from?
    e.g. currently in excel, or currently in another access table?
     
  5. theSeekerr

    theSeekerr Member

    Joined:
    Jan 19, 2010
    Messages:
    2,706
    Location:
    Prospect SA
    Table and field names containing spaces will need to be escaped - IIRC Access uses the SQL Server convention of square brackets, so that's what I've shown.

    The strSQL parameter needs to be a string, right now you've just got non-code text floating around meaninglessly, so something more like this:

    Code:
    strSQL = "UPDATE [FINLDATA MODELS]" &; _
    "SET [BASE TIME] = " & EnteredValue &; _
    "WHERE [FINLDATA MODELS].FINLDATA_ID = " & IDToUpdate
     
  6. OP
    OP
    LayZ

    LayZ Member

    Joined:
    Sep 7, 2011
    Messages:
    88
    Tables within Access

    Thanks you so much! It worked and now I am breezing through, interestingly enough though it won't update BASE TIME if there is no previous value (it's blank). We are moving away from Access in the coming months but this makes my life a lot easier completing the final revision before we move to another database package.
     
  7. OP
    OP
    LayZ

    LayZ Member

    Joined:
    Sep 7, 2011
    Messages:
    88
    Sorry to be a pain, do you know why it wouldn't be updating (BASE TIME) values that are blank?

    EDIT: Figure it out, headaches :(

    There won't be a record in FINLDATA MODELS for a specific MODEL and ID until something is input in to the form. I am trying to make another button that does something like if MODEL not listed for a FINALDATA_ID INSERT FINLDATA_ID and MODEL INTO FINLDATA MODELS
     
    Last edited: May 23, 2018

Share This Page