Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Adding field name to field

    How can I add a field name to a field expression.

    What I have it a piece of code that I have to place under each button to do some work for me - I won't go into detail as I don't believe its required but lets just say the fields with the code are numbered.

    So "Button1 =, Button 2 = etc etc"

    I want to automate the code so at thwe top of each piece of code I have the number, so
    Dim Numb as variant
    Numb = xx (say 1 in this case)

    Then the code reads IF statements, so
    If Fieldxx then etc etc

    How do I add the Numb to the field name so I can then just change the Numb field at the top without rewriting all the code.

    eg:
    Dim Numb as variant
    Numb = 1
    If FieldNumb then....... (this works with Field1)

    Dim Numb as variant
    Numb = 2
    If FieldNumb then....... (this works with Field2)


    Thanks.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you looking for something like
    Code:
    Dim i As Integer
    
    For i = 1 to 3
      Me("Text" & i).Value = i
    Next i
    George
    Home | Blog

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    thanks but I have tried this using my own code but it comes up invalid qualifier?
    any suggestions please

    Code:
    Dim w, b As Integer
    w = 81
    b = 81
        Me("Warranty" & w).Value = w
        Me("Warr" & b & "Box").Value = b
    
    If w <= NowSoon Then
        b.BackColor = 255
        Else
            If w <= Now23y Then
            b.BackColor = 33023
            Else
                If w >= Now23y Then
                b.BackColor = 65280
                    Else
                        b.BackColor = 0
                End If
            End If
    End If
    Don't worry about the NowSoon, etc fields - I have them covered and this works without the integer stuff added.

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Talking does this help u in the wright direction

    For each controle on myform
    if typeoff.controle = button then
    Select case btn.name
    case "button1"
    do something
    case "button2"
    do something
    case "button3"
    do something
    end select
    endif

    next

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    I don't think that helped? Any other suggestions.

    I just need to name the field based on the number above. I do not require it to be counted as this code needs to go into 5 parts of the form, and its easier just to copy and paste then change the top number.

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    actually the easiest way to explain is this;

    warr = ("Warranty") & w
    box = ("Warr") & b & ("Box")

    this allows me to use 'warr' as Warrantyxx (depending on previous code)
    but I need to also use WarrxxBox - is there a way to do the same with this.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    warr = "Warranty" & w
    box = "Warr" & b & "Box"
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2004
    Posts
    287
    thanks that worked - can't believe i missed that! one last issue though:

    Code:
    Dim w, b As Integer
    w = 81
    b = 81
        warr = "Warranty" & w
        warrbox = "WarrantyBox" & b
    
    If warr <= NowSoon Then
        warrbox.BackColor = 255
        Else
            If warr <= Now23y Then
            warrbox.BackColor = 33023
            Else
                If warr >= Now23y Then
                warrbox.BackColor = 65280
                    Else
                        warrbox.BackColor = 0
                End If
            End If
    End If
    Now it errors on the above; (Run time error 424 - object required)
    warrbox.BackColor = 65280

    Any suggestions?
    Last edited by NeilMansell; 11-30-07 at 09:53.

  9. #9
    Join Date
    Nov 2003
    Posts
    1,487
    Try this.....

    Code:
    Dim w, b As Integer
    w = 81
    b = 81
        warr = "Warranty" & w
        warrbox = "WarrantyBox" & b
    
    If Me(warr) <= NowSoon Then
       Me(warrbox).BackColor = 255
    Else
       If Me(warr) <= Now23y Then
          Me(warrbox).BackColor = 33023
       Else
          If Me(warr) >= Now23y Then
             Me(warrbox).BackColor = 65280
          Else
             Me(warrbox).BackColor = 0
          End If
       End If
    End If
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  10. #10
    Join Date
    Mar 2004
    Posts
    287
    great - many thanks that worked... superb!

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    right, I have decided to get fancy a convert all my code to the same sort of thing I was doing above. It's erroring, can anyone see anything wrong with it as I can not see anything (wood from the trees scenarior)

    Code:
    Dim w As Integer
    w = 1
    nameselected = "Name" & w & "Selected"
    Namenumber = "Name" & w
    Usernumber = "User" & w
    Modelnumber = "Model" & w
    MapPosnumber = "MapPos" & w
    DeskLoc = "Town Square"
    warr = "Warranty" & w
    warrbox = "WarrantyBox" & w
    
    
    Dim SQL1 As String
        SQL1 = "UPDATE TBL_Main " & _
              "SET TBL_Main.MapPos = '' " & _
              "WHERE TBL_Main.Name = Me(Namenumber)"
        DoCmd.RunSQL SQL1
    
    Dim SQL2 As String
        SQL2 = "UPDATE TBL_Main " & _
              "SET TBL_Main.MapPos = '1' " & _
              "WHERE TBL_Main.Name = Me(Namenumber)"
        DoCmd.RunSQL SQL2
    
    Dim SQL3 As String
        SQL3 = "UPDATE TBL_Main " & _
              "SET TBL_Main.Location = Me(DeskLoc) " & _
              "WHERE TBL_Main.Name = Me(Namenumber)"
        DoCmd.RunSQL SQL3
    
    Me(w) = DLookup("Name", "TBL_Main", "[MapPos] = Me(w)")
    Me(Usernumber) = DLookup("User", "TBL_Main", "[Name] = Me(Namenumber)")
    Me(Modelnumber) = DLookup("Model", "TBL_Main", "[Name] = Me(Namenumber)]")
    Me(MapPosnumber) = DLookup("MapPos", "TBL_Main", "[Name] = Me(Namenumber)")
    Me(warr) = DLookup("WarrantyExpires", "TBL_Main", "[Name] = Me(Namenumber)")
    
        Dim ctlUser, ctlModel, ctlMapPos, ctlWarranty As Control
        Set ctlUser = Forms!MapTS!Me(Usernumber)
        Set ctlModel = Forms!MapTS!Me(Modelnumber)
        Set ctlMapPos = Forms!MapTS!Me(MapPosnumber)
        Set ctlWarranty = Forms!MapTS!Me(warr)
        ctlUser.Requery
        ctlModel.Requery
        ctlMapPos.Requery
        ctlWarranty.Requery
    Me(nameselected) = Me(Namenumber)
    
    If Me(warr) <= NowSoon Then
       Me(warrbox).BackColor = 255
    Else
       If Me(warr) <= Now23y Then
          Me(warrbox).BackColor = 33023
       Else
          If Me(warr) >= Now23y Then
             Me(warrbox).BackColor = 65280
          Else
             Me(warrbox).BackColor = 0
          End If
       End If
    End If
    ERROR:
    "Run-time error '3085'
    Undefined function 'Me' in expression"

    Highlights the error on line 'DoCmd.RunSQL SQL1' above



    The bottom part of the code works fine (warr, etc)
    it's to top part where the field name is part of a SQL code or part of a DLookup? Any suggestions on how I produce the same results as before so all I need to change is the top few lines which will then change the rest of the code.

    Thanks.
    Last edited by NeilMansell; 12-03-07 at 12:44.

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    Can anyone help please - I have tried everything but nothing works as it should.

    Thanks.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Give us an example from the "everything" you've tried along with any error messages you receive and describe what is and what is not happening.
    George
    Home | Blog

  14. #14
    Join Date
    Nov 2003
    Posts
    1,487
    Code:
    Dim w As Integer
    w = 1
    nameselected = "Name" & w & "Selected"
    Namenumber = "Name" & w
    Usernumber = "User" & w
    Modelnumber = "Model" & w
    MapPosnumber = "MapPos" & w
    DeskLoc = "Town Square"
    warr = "Warranty" & w
    warrbox = "WarrantyBox" & w
    
    Dim SQL1 As String
    SQL1 = "UPDATE TBL_Main " & _
               "SET TBL_Main.MapPos = '' " & _
               "WHERE TBL_Main.Name ='" & Me(Namenumber) & "'"    
    DoCmd.RunSQL SQL1
    
    Dim SQL2 As String
    SQL2 = "UPDATE TBL_Main " & _
               "SET TBL_Main.MapPos = '1' " & _
               "WHERE TBL_Main.Name ='" & Me(Namenumber) & "'"    
    DoCmd.RunSQL SQL2
    
    Dim SQL3 As String
    SQL3 = "UPDATE TBL_Main " & _
               "SET TBL_Main.Location = '" & Me(DeskLoc) & "' " & _
               "WHERE TBL_Main.Name ='" & Me(Namenumber) & "'"
    DoCmd.RunSQL SQL3
    
    Me(w) = DLookup("Name", "TBL_Main", "[MapPos] ='" & Me(w) & "'")
    Me(Usernumber) = DLookup("User", "TBL_Main", "[Name] ='" &Me(Namenumber) & "'")
    Me(Modelnumber) = DLookup("Model", "TBL_Main", "[Name] ='" &Me(Namenumber) & "'")
    Me(MapPosnumber) = DLookup("MapPos", "TBL_Main", "[Name] ='" &Me(Namenumber) & "'")
    Me(warr) = DLookup("WarrantyExpires", "TBL_Main", "[Name] ='" &Me(Namenumber) & "'")
    
    Dim ctlUser, ctlModel, ctlMapPos, ctlWarranty As Control
    Set ctlUser = Forms!MapTS!Me(Usernumber)
    Set ctlModel = Forms!MapTS!Me(Modelnumber)
    Set ctlMapPos = Forms!MapTS!Me(MapPosnumber)
    Set ctlWarranty = Forms!MapTS!Me(warr)
    ctlUser.Requery
    ctlModel.Requery
    ctlMapPos.Requery
    ctlWarranty.Requery
    
    Me(nameselected) = Me(Namenumber)
    If Me(warr) <= NowSoon Then
       Me(warrbox).BackColor = 255
    Else
       If Me(warr) <= Now23y Then
          Me(warrbox).BackColor = 33023
       Else
          If Me(warr) >= Now23y Then
             Me(warrbox).BackColor = 65280
          Else
             Me(warrbox).BackColor = 0
          End If
       End If
    End If
    Assuming all fields are of the TEXT DataType

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  15. #15
    Join Date
    Mar 2004
    Posts
    287
    Thanks but now I now get this error:
    "Run-time error 438
    Object doesn't support this method or property"


    With the following line in the code highlighted:
    Me(w) = DLookup("Name", "TBL_Main", "[MapPos] ='" & Me(w) & "'")

    Any suggestions why it should be doing this?

Posting Permissions

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