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-07-27T23:13:16+00:00

    Should all FKs be integers and all PKs be autonumbered?

    Often an autonumber primary key, and corresponding long integer foreign keys will be the most convenient, but it's important to understand that this does not absolve you from defining other columns as candidate keys if the values in such columns are distinct.  Take a table of American states for example.  You might give this an autonumber StateID primary key and short text columns for the state name in both full and abbreviated forms.  As these must both contain distinct values, each is a candidate key, so must be defined as such by being indexed uniquely (no duplicates).  The same is true of a candidate key composed of two or more columns, e.g.  ProductID and OrderID in an OrderDetails table which as an OrderDetailID primary key.  ProductID and OrderID must be included in a single unique index to  define them as a composite candidate key. Otherwise data integrity is at risk.

    Note also that there are situations where a 'natural' primary key such as a state name can have significant advantages over the use of a 'surrogate' autonumber key.  Firstly it can reduce the complexity of a query by not requiring the States table to be included in a query to show the state name.  A natural key also makes the use of correlated combo boxes easier.  This is where the selection of a value in one combo box restricts the available items listed in another combo box.  If such combo boxes are used in a form in continuous forms view, the use of natural keys makes this much simpler.  If, on the other hand, a 'surrogate' numeric key is used it is necessary to use 'hybrid' controls where a text box is superimposed on a combo box to give the appearance of a single combo box control.  Otherwise the combo boxes in rows other than the current row can appear to be empty.  No data is lost, it's just hidden.

    Was this answer helpful?

    0 comments
  2. ScottGem 68,840 Reputation points Volunteer Moderator
    2022-07-27T18:29:58+00:00

    It seems like if I just tie everything to the GrandID in the main table I could probably go from there, but I'm not clear on how the form will actually play out so I'm trying to get a better feel for the dependencies shenanigans before I go to that.

    There are two factors at play here. Tables that are children of the Grant parent and tab les that supply values to the Grant parent. So, for example, the AssignedPI table I suggested is a child of Grants. While the ResearchType lookup table supplies values to it. So the ResearchTypeID is an FK in Grants and the GrantID is a FK in the AssignedPI table.

    You might want to add your tables to the Relationships Window (Database ribbon) And then posgt a scfreenshot of the Window for us to see and comment on.

    Was this answer helpful?

    0 comments
  3. ScottGem 68,840 Reputation points Volunteer Moderator
    2022-07-27T17:28:49+00:00

    "If I did its own table, I'm not sure what I would put into it besides GrantID as an FK (I think?) and the amount of indirect funding requested"

    If the funding source will always be only direct or indirect a lookup table might be superfluous. But what if a grant was both Direct or Indirect? Is that possible? Then you might want a child table that indiacted the amount of each type.

    You should have a Status lookup table. Then the Status field in the Grants table would be an FK to the Status lookup table. And yes you can then filter reports depending on status. Generally Reports are based on multi-table queries (unlike forms). since a table can often contain a lokt of FKs that are just code numbers, you want to join to the child tables to get the descriptive values.

    I like that cartoon, btw. I dislike Irish and Italian names that often contain an apostrophe because they cause havoc in filtering queries. That's another reason to just use a coded PK it helps filter records.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2022-07-27T17:26:46+00:00

    Update: Thanks so much for all of this help! I made the tables and I'm trying to understand how the dependencies work using this:

    https://docs.microsoft.com/en-us/office/troubleshoot/access/define-table-relationships

    It seems like if I just tie everything to the GrandID in the main table I could probably go from there, but I'm not clear on how the form will actually play out so I'm trying to get a better feel for the dependencies shenanigans before I go to that.

    Was this answer helpful?

    0 comments