Results 1 to 9 of 9
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    SYNTAX Help (Again) (A2K SR1)

    Table Property_Master contains a field called Tag. The following data has been entered into the Property_Master table in the field Tag:
    1000, 1000A, 1000B, .... 1000Z.

    In a form, using a combo box, the user selects a Tag, say 1000. The form then returns all other values of the tag that end as a letter, that is, 1000A, 1000B, ... 1000Z.

    The following code works if you want to do it the long way by inputting it 26 times (not very efficient)

    Dim a, b, c, d, e, f, g, H, I, J, K, L, M, N, O, P, Q, R As Integer
    Dim S, T, U, V, W, X, Y, Z As Integer

    ' For the tag selected, check to see if there is a similar tag ending in the letter A, count = 1
    a = DCount("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'A'")

    'If 1000A exists, then set TagA.Value to 1000A.
    If a = 1 Then
    Me.Asset_TagA.Value = DLookup("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'A'")
    End If

    b = DCount("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'B'")
    If b = 1 Then
    Me.Asset_TagB.Value = DLookup("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'B'")
    End If

    What I want to do is loop through all of these values so I dont write 26 If then statements but I cant get the syntax to work.

    Dim x as integer
    Dim cc as integer

    For x = a to z
    cc = DCount("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'A'")
    If cc = 1 Then
    Me.Asset_TagA.Value = DLookup("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'A'")
    End If
    Next X

    Question1 - In the CC = Dcount....., How do I correctly reference .VALUE &'A'") to .Value and the current value of X, i.e, x is a through z?
    Question 2 - In the If statement, Me.Asset_TagA.Value, How do I Reference TagA.Value to the current value of X, i.e., TagX.value where X is a to z?

    Normally, I would have a master Tag table linked as a one to many to a sub Tag table for these items.... Ultimately, I will have to get other data associated with these sub tags on the form as well. The users do not want to have to manually input this data.....

    Help...... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SYNTAX Help (Again) (A2K SR1)

    Hi Gary,
    On looking at your code it seems that you don't want the value of X but the character X itself.
    If that's what you want then setup a string constant "abcde...xyz" and reference this.

    If that's not whta you want, and you say you want the value of X, is this always 1 character or is it more than one character?
    If it is 1 character then you could concatenate all of a thru z into a 26 character and reference it that way, or you could call a form with the required letter followed with the 26 values as arguments. eg GetValueofX(LetterRequired,a,b,c,d,e,f,...x,y,z)
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: SYNTAX Help (Again) (A2K SR1)

    Why can't you just build a query like this, and set the record source of the form to the query.
    Attached Images Attached Images
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SYNTAX Help (Again) (A2K SR1)

    I'm thoroughly confused. You want to take a user selected "tag" and return all records that contain all variations on that tag where the strings begin with the selected value followed by at least one alpha character, right? I was confused by this statement:
    <hr>The form then returns all other values of the tag that end as a letter, that is, 1000A, 1000B, ... 1000Z<hr>
    Returns them where, as records, as a list, as a recordset, or what? And what are Me.Asset_TagA, Me.Asset_TagB, etc.? Are there 26 of those? Are they bound or unbound, and what is it you're doing with them? If you're populating the combo from the Property_Master table, don't you already *know* the tag exists?

    Is there some reason you aren't updating the subtag with a query? Even if you had to run a query 26 times, it would be less confusing that this approach.
    Charlotte

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

    Re: SYNTAX Help (Again) (A2K SR1)

    I don't understand what you're doing, but it might be useful to know that you can refer to controls as a string by using Me.Controls("controlname") or even Me("controlname"). You can build the controlname string by concatenation in VBA.

    Your code could be something like this:

    Dim x As Integer
    Dim cc As Integer

    For x = 1 To 26
    cc = DCount("[TAG]", "PROPERTY_MASTER", _
    "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE & '" & Chr(64 + x) & "'")
    If cc = 1 Then
    Me.Controls("Asset_Tag" & Chr(64 + x)).Value = DLookup("[TAG]", "PROPERTY_MASTER", _
    "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE & '" & Chr(64 + x) & "'")
    End If
    Next x

    But I suspect that something much simpler could be used.

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SYNTAX Help (Again) (A2K SR1)

    Charlotte,

    If you weren't thoroughly confused by my question then I would feel something is wrong with my post. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    The form allows the user to select a tag, say 1000. If the tag has subtags, say 1000A, 1000B, etc then the user requires a list of these tags on the form as well. The value of each subtag is being shown in an unbound textbox. Ultimately, all of the values on the form will be used to populate a table and produce a report.

    This probably isn't much clearer but thanks for your help.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SYNTAX Help (Again) (A2K SR1)

    Hans,

    Thanks for the information. You solved my syntax question.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SYNTAX Help (Again) (A2K SR1)

    John,

    I thought of this or using a listbox as well. Thanks for the idea.
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SYNTAX Help (Again) (A2K SR1)

    POST IMMEDIATELY EDITED.

    A huge thanks once again. I did not know that you can refer to controls as a string as indicated. You and Charlotte are my hero's. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    ... even if my orignal question was incoherent...

    BTW, The working code is as follows:

    Private Sub Asset_Tag_Change()
    Dim X, CC As Integer

    'Get subtag data for each selected tag. There can be up to 26 subtags.
    'A subtag is defined as any tag ending in a letter. I.e. 1000A, 1000B are subtags of 1000

    'Check to see if tag selected is a subtag (ends in letter). If so, end process.
    'NEED TO ADD CODE

    'Begin Process to get subtags

    'Loop through letters after a tag is selected in the combobox to get subtags.

    For X = 1 To 26

    'CC = 1 for each subtag ending in a letter
    CC = DCount("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'" & Chr(64 + X) & "'")
    If CC = 1 Then

    'If subtag exists, set value to textbox for future use
    Me.Controls("Asset_Tag" & Chr(64 + X)).Value = DLookup("[TAG]", "PROPERTY_MASTER", "[PROPERTY_MASTER.TAG] = [FORMS]![AssetAcquisitionDataEntry]![ASSET_TAG].VALUE &'" & Chr(64 + X) & "'")
    Else

    ' If subtag does not exist or jumps from a to m, set textbox to " "
    Me.Controls("Asset_Tag" & Chr(64 + X)).Value = " "
    End If
    Next X

    Refresh

    End Sub


    Once again, thanks.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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