Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I took a look - I don't experience most of those messages but maybe that has something to do with having to remove a reference that I don't have in Program Files(x86).
    Anyway, you need to step through your code and really think about what you want each step to do, what you're giving it/telling it, and watch what it is exactly doing. You also need to realize that when a procedure ends, whatever you think you created/set in it is gone IF those variables are local to that procedure. F'rinstance, you create a recordset (see post 4) then End Sub and the recordset variable scope is to that procedure only, so totally useless code.

    Then you use form current to create a recordset. If that rs only ever returns at most 1 record, then just bind the form to the query since you are using nav buttons to move from record to record. Right now, each time you move to another record you're running the event again to do what - get the next record? I don't know yet because I'm in the middle of stepping.



    OK, next you attempt to pass a form to another sub and set a variable to its recordset count - you cannot pass a form and deal with its recordset. You can only deal with its recordsetclone but you can't get at it from there (and you're trying to do that at least twice). Even if you could the count would be 1 anyway, because you never did Move Last before that. So leaving that procedure (since the rest of it doesn't run anyway, at least for me) you have error handling blocks that your code goes into automatically because you don't exit sub before that. Thus they run anyway.

    Most of your issues seem to stem from not binding your form to a table or query and let Access do the heavy lifting. Instead, you take the approach of building a (sort of) Rube Goldberg machine. That might be OK if you had developed robust vba skills, but that is not really the case here. One day that might be a different story, but you have to learn to crawl before you can walk. I hope you don't take any of that as harsh criticism, no more than I would if you had seen my investment management skills at work. You would tell me to contract that out, which is maybe something you'd want to consider since as long as you're dabbling in this, you are losing time that you might better devote to your investment dealings?

    Last,
    I DO NOT HAVE A MEMBER TABLE
    oh, yes you do. You joined "Sponsers" to "Sponsers" and aliased one as "Members". Basically the same thing as having a table by that name.
    Last edited by Micron; 11-12-2022 at 03:07 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #17
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Your On Current Event for the Form frmMembersNew as shown below uses this line:-

    Set rst = db.OpenRecordset("QryMemNav")

    The "qryMemNav" has two tables "Members" and "Sponsors", however you do not have a table named "Sponsors" in the database?

    Code:
    Private Sub Form_Current()
    On Error Resume Next
    Dim db As Database
    Dim rst As Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("QryMemNav")
        On Error GoTo Form_Current_Error
        cboSearch = Null
        'update the custom navigation record count
        Call subCommon_Form_Current(Me.Form)
    'Form_Current_EXIT:
    '    Exit Sub
    Form_Current_Error:
    MsgBox "error" & " " & Err
        Select Case Err
            Case Else
                MsgBox Err & ", " & Err.Description & " form " & Me.Name
        End Select
    '    Resume Form_Current_EXIT
    End Sub
    You are using Lookup fields in your tables which are not recommended.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #18
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The "qryMemNav" has two tables "Members" and "Sponsors", however you do not have a table named "Sponsors" in the database?
    Read the last line of my prior post.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Micron

    I just did and agree
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #20
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Agreed. i have thought long about what I am doing....One such question is: "When I am in Query design mode(editing an existing query) I notice that Access does not always prompt Save Changes?' . Des ctl-s cause the sql to update?I will upload the accdb file after I dl win ram

  6. #21
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Thank toy for your thoughts. Lots to 'chew on' The members table is self joined to a copy of itself. Currently it lookslike my query is corrupted.....in design mode I have 3 fields but Access shows all fields in inDatasheet mode........I am trying to get around the WINRARcreated zip size of 5 mb.........Thanks again...

  7. #22
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Admittedly, I find Access working in seeminly mysterious ways; and realize almost always that the result is my fault. this situation leaves me guessing about the origin /root cause of my misunderstanding by cruising through the Access books or Youtube video. Your thoughts, nomatter how critical, are appreciated. thanks...

  8. #23
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    n design mode I have 3 fields but Access shows all fields in inDatasheet mode
    One of them probably uses the wildcard option, which will pull in all fields.

    As for you zip issue, doesn't some sort of zip utility come with Windows? I don't know because I use 7zip. If you can't get it down small enough, remove whatever isn't needed for your post. If the db has images in it, that's probably your issue. If you embed images rather than link them, that's another possible reason. Then there is a drop box but some won't dl from a drop box.

    Some responders here can't open rar files IIRC.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #24
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    doesn't some sort of zip utility come with Windows?
    it does, Right click on the file, select Send To>Compressed (zipped) folder

  10. #25
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    thanks dave

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-20-2021, 07:31 PM
  2. Replies: 6
    Last Post: 12-03-2013, 11:14 PM
  3. How to Select second Recordset from first recordset
    By FrustratedAlso in forum Programming
    Replies: 28
    Last Post: 05-10-2012, 05:45 PM
  4. Replies: 2
    Last Post: 03-08-2012, 12:59 PM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums