GoToRecord stopped working during Form_Load()

Anonymous
2022-07-25T14:54:36+00:00

TL;DR - I want to inputbox("What recordID") then doCmd to go to that ID. It was working fine, then it stopped and I can't make it work again. error is 2046 DoCmd isn't available right now. Now idea why.

Private Sub Form_Load()

myRecord = InputBox("What's the recordID?")

DoCmd.GoToRecord , , acGoTo, myRecord

End Sub

This worked for the first hour or so I was working. Eventually after I added a command button for SOME REASON it stopped working. I deleted the button. Still won't work. Nothing I do will make it work and I've been playing with such a small stupid thing for like 30 minutes. Needless to say, I am not a programmer by trade or passion - this is just something that I know needs to be done at work and no one else knows how to do it. Searching the internet, the only thing I can think of is that the records aren't editable except I've checked both w/ the design form gui and programmatically that they're editable and they are. I'm completely at a loss. Sorry for the dumb question - I'm sure it's something simple, but not being able to figure it out is stopping me from working.

Edit: Even if I put in (for example) a command button using the built in functionality to go to the first/last/next/etc. record, the button doesn't do anything. All of this worked before; I have no idea what changed it. I did not touch the form_load() code at all; I wasn't even working on that when it broke.

Microsoft 365 and Office | Access | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
ScottGem 68,840 Reputation points Volunteer Moderator
2022-08-08T18:32:10+00:00

There are a couple of different ways to do this. You can navigate to a specific record by using the navigation buttons at the bottom of the form. But that can be cumberson.

Most developers wil use some sort of search facility to postion the form at the record they want to see. The combobox wizard can create such a search combo (the third option). You can further customize it after the wizard does it's job by modifying the Rowsource. The key is the code the wizard generates to position the form at the selected record. Another method is to create a search form to find the record then open another form filtered for the record you want.

Was this answer helpful?

1 person found this answer helpful.
0 comments

57 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-08-05T13:07:45+00:00

    For some reason once I add data from multiple tables to a form, the form either populates blank or populates w/ combo boxes that have 1,2,3,4... instead of the data from the field I need. This problem doesn't come up when I don't use data from other fields. Any idea what's happening?

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-08-03T18:29:53+00:00

    I can't think of a scenario where a sponsor would be a subsponsor and vice/versa

    OK, But still have one Sponsor table with a field to indicate whether they would be a sponsor or a sub.

    Welp, that's embarrassingly simple. Yeah that works better. I'll just add a "sub of" field or something to indicate who they belong to.

    I don't understand how to implement it yet

    You implement it with the use of a Junction table. From a data entry standpoint you implement it with a mainform/subform linked on parent (PK) to child (FK) keys. When you enter a record in a linked subform, the FK is automatically populated.

    I will research this more, probably tomorrow. I get what you're saying, but I'm afraid due to my inexperience it's a bit like saying how do you make a soufflé? With such and such ingredients and an oven. Very simple to a chef, but I'm more like that 7-year-old trying to figure out how the can opener works so he can eat Spaghettios than I am to a chef. That being said, at least I'm advanced enough now to realize that there are better foods to be cooked than Spaghettios😅. Thanks for that!

    Was this answer helpful?

    0 comments
  3. ScottGem 68,840 Reputation points Volunteer Moderator
    2022-08-03T16:23:07+00:00

    I can't think of a scenario where a sponsor would be a subsponsor and vice/versa

    OK, But still have one Sponsor table with a field to indicate whether they would be a sponsor or a sub.

    I don't understand how to implement it yet

    You implement it with the use of a Junction table. From a data entry standpoint you implement it with a mainform/subform linked on parent (PK) to child (FK) keys. When you enter a record in a linked subform, the FK is automatically populated.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-08-03T14:12:46+00:00

    By the same reasoning, I probably need to make another table for subsponsors

    No, ONE sponsors table. I'm guessing a sponsor can also be a subsponsor and vice versa. So whether a sponsor is a main or sub is identified where they are assigned. So one table.

    I can't think of a scenario where a sponsor would be a subsponsor and vice/versa. For example, if the sponsor is the NIH (National Institutes of Health), the subsponsor could be NIA (the National Institute on Aging). Because the NIH is the parent organization, it will always be the sponsor and NIA (etc.) will always be subsponsors. It's like a country-state type relationship. If it can be reversed, the subsponsor was never a subsponsor to begin with and the user screwed up.

    I'm not really sure what we'd need a PK for though

    EVERY table needs a PK. You need something that uniquely identifies a record. This can be a single field or combination (though I'm not a fan of composite keys) or it can be a surrogate key (like an autonumber), but every table must have one.

    I will make it magically know from GrantID relationship the PIIDs of researchers

    Its not magic, its called a many to many relationship. A Grant can have multiple PIs and a PI can be associated with multiple Grants. So you use, what's called, a Junction table to model that relationship. That's what tblPIAssigned is.

    It will remain magic in my mind until it makes sense 😅. I get conceptually how it works, but I don't understand how to implement it yet. I was planning on diving into Ken's db today to figure it out but for some reason I'm unable to download files from the drive he linked. Hopefully he'll respond and I'll be able to play with his examples tomorrow. If not, google. At least my tables seem to be in order now.

    pull quarters from submission dates

    How about a table like this:

    tblMth2Qtr

    MonthID (PK)

    FiscalQtr

    This table has 12 records. One for each month with the corresponding fiscal quarter. Then all you need is either a DLookup or Join in a querter to pull the Quarter.

    That'll be on my to-do list for when my way inevitably breaks or I've figured out how to do these relationships properly. I'm literally shocked that the calculated field way worked b/c nothing complex I try in calculated field ever works. Literally I can't get the most simple between statement to work. If(myField between 1 And 3, "Yes", "No") won't even attempt to run no matter how many different ways I do it. It doesn't matter much b/c I can do it w/ Iif + and/or, but omg it's frustrating.

    Was this answer helpful?

    0 comments