Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    specific record query (access 2003)

    Building a record based on two different queries. Looking for a way to open second query (tst) to a specific record [item number] so that fields can be used instead of reading each record until a match is found per code below.


    'Table/Query Lookup for mating assemblies
    IN=[item number]
    Set tst = dbs.OpenRecordset("query-inven-limit", dbOpenDynaset)
    Do While Not tst.EOF
    tst.Edit
    If tst![item Number] <> in Then GoTo jump_tst
    .
    .
    .
    loop

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

    Re: specific record query (access 2003)

    Try

    Set tst = dbs.OpenRecordset("SELECT * FROM [query-inven-limit] WHERE [Item Number]=" & [Item Number], dbOpenDynaset)

    This should open a record set with only the record(s) that have the required item number.

  3. #3
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: specific record query (access 2003)

    There are cases where the item number is not in .tst What would be the message and process if query does not have a record found?

    Set rst = dbs.OpenRecordset("query-inven-raw", dbOpenDynaset)
    Set tsr = dbs.OpenRecordset("query-inven-scrub", dbOpenDynaset)
    Do While Not rst.EOF
    rst.Edit
    BUILD_RECORD:
    tsr.AddNew
    tsr![Item Number] = rst![Item Number]
    tsr![pds fg] = rst![pds fg]
    QIN = tsr![Item Number]
    'Table/Query Lookup for mating assemblies
    Set tst = dbs.OpenRecordset("SELECT * FROM [query-inven-limit] WHERE [Item Number]=" & QIN, dbOpenDynaset)
    tst.Edit
    tsr![mate] = tst![mate]
    GoTo close_tst

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

    Re: specific record query (access 2003)

    After

    Set tst = dbs.OpenRecordset("SELECT * FROM [query-inven-limit] WHERE [Item Number]=" & QIN, dbOpenDynaset)

    you can use

    If tst.EOF Then
    ' No matching record(s)
    MsgBox "There are no records for this QIN", vbExclamation
    Else
    ' Code to edit record
    ...
    End If

  5. #5
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: specific record query (access 2003)

    Finally able to get back to this. It appears that the open .TSR record is not opening correctly because the ITT and FG strings are coming back empty.
    The Qin string is valid and there is a matching [item number] in .tsr query for it. I suspect I have something wrong in Set .tsr command

    Set tst = dbs.OpenRecordset("query-inven-limit", dbOpenDynaset)
    Do While Not tst.EOF
    tst.Edit
    Qin = tst![item number]
    Set tsr = dbs.OpenRecordset("SELECT * FROM [query-inven-scrub] WHERE [Item Number]=" & Qin, dbOpenDynaset)
    itt = [item number]
    fg = [mf fg]
    tsr![mate] = tst![mate]
    Stop

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

    Re: specific record query (access 2003)

    Shouldn't the lines

    itt = [item number]
    fg = [mf fg]

    be

    itt = tsr![item number]
    fg = tsr![mf fg]

  7. #7
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: specific record query (access 2003)

    No change, still "blank". There are values in those fields which leads me to believe it is not opening.

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

    Re: specific record query (access 2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: specific record query (access 2003)

    Code reads the RAW file and creates the SCRUB file. LIMIT file contains MATE item numbers that need to be added to SCRUB. That is where it is breaking.
    The limit file is updated periodically with mates.
    Attached Files Attached Files

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

    Re: specific record query (access 2003)

    The Item Number field is not a number field as I had assumed, but a text field. The line that opens tsr should be changed to

    Set tsr = dbs.OpenRecordset("SELECT * FROM [query-inven-scrub] WHERE [Item Number]=" & Chr(34) & Qin & Chr(34), dbOpenDynaset)

    Chr(34) is the double quote character ".

    Warning: the code then tries to update the tsr record without having entered edit mode (there is no line tsr.Edit).
    And it is still possible that there is no record for a specific QIN, so you have to check for that:

    Set tsr = ...
    If Not tsr.EOF Then
    tsr.Edit
    itt = tsr![item number]
    fg = tsr![mf fg]
    tsr![mate] = tst![mate]
    tsr.Update
    End If
    tsr.Close

    Finally, the recordset tsr has already been closed within the loop, so you can't close it any more at the end of the code: remove the line tsr.Close immediately above End Sub.

  11. #11
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: specific record query (access 2003)

    BINGO! The Chr(34) & Qin & Chr(34) did it. QIN will always be found in TSR but I added the IF NOT anyway.
    On the real file, it is 200+ records. Appreciate the help.

Posting Permissions

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