Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    58

    Unanswered: help with update query

    I need to update my table. I have created a query and need to translate the following into the criteria part of the Access update query.

    If Left$(StationNo, 4) = "ICT1" Then location = "ICTSuite1"
    If Left$(StationNo, 4) = "ICT3" Then location = "ICTSuite3"

    StationNo and location are field names.

    Example data:

    STATION NO LOCATION
    ICT1Suite023 ICTSuite1
    ICT2Suite005 ICTSuite2
    ICT3Suite003 ICTSuite3
    ICT4Suite001 ICTSuite4

    The reason i am doing this is i have just about finished an inventory program in VB6 and need to dump all of our Excel data into access.
    I have over 400 computers so this would take ages manaually.

    Many thanks

    neil

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to answer your question directly
    run a series of update queries which has "ICT1" as the condition and "ICTSuite1" as the update value, then ICT3........

    however I think you need to revisit your table design, you are duplicating information in a table, which is a no no. What happens if ICT Suite 1 is demolished & all machines are moved to a new location. Using your current design you would have to run another series of update queries

    Instead I would suggest you have a separate table which maps a location code to the dinky name
    eg tblLocations, WHERE LocationID=0, Location="ICT suite1"
    define a relationship between the location & equipment tables
    whilst you are at it you may also want to define an equipment type, manufacturer etc to yur table design.

  3. #3
    Join Date
    Oct 2003
    Posts
    58
    hi,

    Thanks for your reply.

    "run a series of update queries which has "ICT1" as the condition "

    How do i run a condition?
    what do I put in the criteria box of the field for it to become a condition.
    The trouble here is the "stationID" data is in the format of "ICT1Suite023","023" being a station number. This is why I was thinking of using the left$ command to say, if the first 4 letters of the string are ICT1 then put "ICTSuite1" in the location field, which is a FK BTW.
    I do have a locations table and a make, model etc..

    I know this is messy. My VB6 program is finished and i have been trialing it with test data. Rather than add all the data in manually I was hoping to dump it all in.

    So, in a nutshelll all the data is in my equipment table. the location field(fk) has "ict1suite023" in every cell of the field.
    I simply want to search the "stationId" field and where it finds "ICT1" as the first 4 characters in the string then put "ICTSuite1" in the location field of that record.
    Similarly if it finds "HIST" then it will insert "History" in the location field.

    I hope this makes sense. its hard to explain.

    thanks

  4. #4
    Join Date
    Oct 2003
    Posts
    58

    got there

    Sorted it!

    I just did it from VB6 using sql

    Cheers

  5. #5
    Join Date
    Aug 2004
    Posts
    178
    put this code into a butten on a form



    'Once you have created the module, then you will need to attach the following code to a command button (or label, graphic etc.):

    '***************** Code Start ***************
    'Assign this to the OnClick event of a command button (or double-click event
    'of a label or graphic) named "bDisableBypassKey"
    'Change the "TypeYourBypassPasswordHere" default password to your password

    'Private Sub bDisableBypassKey_Click()
    On Error GoTo Err_Command26_Click
    Dim strInput As String
    Dim strMsg As String
    Beep
    strMsg = "Do you want to enable the Bypass Key?" & vbCrLf & vbLf & _
    "Please key the programmer's password to enable the Bypass Key."
    strInput = SetTimer(Me.hwnd, NV_INPUTBOX, 10, AddressOf TimerProc)
    strInput = InputBox(Prompt:=strMsg, title:="Disable Bypass Key Password")

    If strInput = "password in here" Then
    SetProperties "AllowBypassKey", dbBoolean, True
    Beep
    MsgBox "The Bypass Key has been enabled." & vbCrLf & vbLf & _
    "The Shift key will allow the users to bypass the startup options the next time the database is opened.", _
    vbInformation, "Set Startup Properties"
    Else
    Beep
    SetProperties "AllowBypassKey", dbBoolean, False
    MsgBox "Incorrect ''AllowBypassKey'' Password!" & vbCrLf & vbLf & _
    "The Bypass Key was disabled." & vbCrLf & vbLf & _
    "The Shift key will NOT allow the users to bypass the startup options the next time the database is opened.", _
    vbCritical, "Invalid Password"
    Exit Sub
    End If
    Exit_Command26_Click:
    Exit Sub
    Err_Command26_Click:
    MsgBox "Command26_Click", Err.Number, Err.Description
    Resume Exit_Command26_Click
    End Sub

    then creat a module and put this code in it



    '***************** Code Start ***************
    'Copy this function into a new public module.



    Public Function SetProperties(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer

    On Error GoTo Err_SetProperties

    Dim db As dao.Database, prp As dao.Property

    Set db = CurrentDb
    db.Properties(strPropName) = varPropValue
    SetProperties = True
    Set db = Nothing

    Exit_SetProperties:
    Exit Function

    Err_SetProperties:
    If Err = 3270 Then 'Property not found
    Set prp = db.CreateProperty(strPropName, varPropType, varPropValue)
    db.Properties.Append prp
    Resume Next
    Else
    SetProperties = False
    MsgBox "SetProperties", Err.Number, Err.Description
    Resume Exit_SetProperties
    End If
    End Function
    '***************** Code End ***************

Posting Permissions

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