Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data from another table (2000)

    I have the following code in an Access 97 database:

    Private Sub GroupNumber_AfterUpdate()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Handler

    'Only do this if user is on new record
    If Me.NewRecord Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset _
    ("SELECT ClientNumber, GroupName FROM tblSpecialistinfo WHERE GroupNumber='" & Me!GroupNumber & "'")


    'Did we get a match?
    If rst.NoMatch = False Then
    'Copy some fields
    ClientNumber = rst!ClientNumber
    GroupName = rst!GroupName
    End If
    End If

    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    I want to do a similar process in an Access 2000 database that instead of using the table behind the form to match up to, I want to bring in the fields from another table or Query. When someone types in the Client Number, I want a field on the form to be populated if the Client Number matches the Client Number in this other table or Query. I wasn't sure about the Query. Is it possible to use the above code (with modifications) to do this?

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

    Re: Data from another table (2000)

    Why not simply use a Dlookup instead?
    Charlotte

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Data from another table (2000)

    Linda,

    When you opened the recordset, you specified a table name as part of your query. The fact that the table may also have been behind your form is coincidental, and played no part in whether or not your query worked. So, your technique will work in any version of Access with any tables.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from another table (2000)

    Wil it work with a query?

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Data from another table (2000)

    If I have understood what you are asking, then No, it won't matter if you use a query as the basis for your recordset. The recordset you open is independant of anything else.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from another table (2000)

    I put the following code behind the field ClNum:

    Private Sub ClNum_AfterUpdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Handler

    'Only do this if user is on new record
    If Me.NewRecord Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset _
    ("SELECT ClientName, FAContracts FROM ClientQry WHERE ClNum='" & Me!ClNum & "'")


    'Did we get a match?
    If rst.NoMatch = False Then
    'Copy some fields
    ClientName = rst!ClientName
    FAContracts = rst!FAContracts
    End If
    End If

    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    When I enter a Client Number in the ClNum field and tab to the next field, I get an error - Too few parameters. Expected 1 .
    When I hit the tab key, I want the ClientName and FAContracts field to populate if the ClNum matches the CLNum in the ClientQry. ClientQry is a totals query.

    What am I doing wrong? My VBA knowledge isn't very good. Thanks for your help

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

    Re: Data from another table (2000)

    I don't think the NoMatch can be used here, I think it is reserved to work with the FindFirst, etc...
    You should use the Eof test instead.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from another table (2000)

    I got the code to work but when I enter a Client Number and it doesn't have a match in the ClientQry, it gives me an error message - "The value you entered isn't valid for this field". I would like the error to say - "No Current Record". The error message for the 97 database that uses this code says "No Current Record".

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Data from another table (2000)

    You can't use the rst.noMatch here. You only use this after using a .Find method. Your recordset consists of a WHERE clause, therefore the recordset will contain either no records or it will contain 1 record (can more than 1 record meet the condittions?). To check for "empty", just check for:
    If rst.RecordCount = 0 then
    .... your code here....
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from another table (2000)

    I changed the code to what you sent but I still get the same error message. I don't understand why the code works in a 97 database but it gives different results (as far as error code) in the 2000 database. Maybe I am trying to put a round peg in a square hole......

    Now for another problem - How do I revise the code so that it updates the record to whatever is in the query - not just for a new record. I need specifics since I am not a programmer. Thanks for your help

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

    Re: Data from another table (2000)

    Actually the code *doesn't* work in a 97 database. In both 97 and 2000, NoMatch is used with Find methods. The only reason it might appear to work is since you aren't using Find, NoMatch is always going to return a False and the rest of the code would then execute As far as an error message is concerned, 2000 and later use a different Visual Basic Editor and are less forgiving than 97's VBE.
    Charlotte

  12. #12
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Data from another table (2000)

    Hi Linda,
    In addition to Charlotte's and Mark's comments, I think the error you are getting isn't related to the method of testing - I believe you are trying to assign a Null value to the ClientID field, and Access 2000 is telling you you can't do that. The lines of code
    <font color=blue><font face="Georgia">
    ClientName = rst!ClientName
    FAContracts = rst!FAContracts
    </font face=georgia></font color=blue>
    are presumable trying to copy the contents of the fields from the recordset to controls on a form, but the recordset was empty, so there isn't any data to copy. Does that make sense?
    Wendell

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Data from another table (2000)

    What error are you getting? I'd have to see your new code. Also, check your references to make certain you have one for DAO; Access2000 by default only has one for ADO.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data from another table (2000)

    I did reference DAO. The code I am using now is: What Wendell said makes sense. Maybe I should start from scratch. I took out the refernce to new record and now it updates the way I want it to. It is just that error message "The Value you entered isn't valid for this field" . I would like it to say "No Match" or something to that effect.

    Private Sub ClNum_AfterUpdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Handler

    'Only do this if user is on new record
    'If Me.NewRecord Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset _
    ("SELECT ClientName, FAContracts FROM ClientQry WHERE ClNum='" & Me!ClNum & "'")


    'Did we get a match?
    If rst.NoMatch = False Then
    'Copy some fields
    ClientName = rst!ClientName
    FAContracts = rst!FAContracts
    End If


    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

  15. #15
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Data from another table (2000)

    Again, I don't think the .NoMatch property will work here, as you didn't use a Find method (FindFirst, etc.). To see if the recordset contains any records, check either rst.EOF = True or if the rst.RecordCount property > 0.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Page 1 of 2 12 LastLast

Posting Permissions

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