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. ScottGem 68,840 Reputation points Volunteer Moderator
    2022-08-08T15:25:59+00:00

    So, I'm not seeing a subform in your image. Also, why do you think "have it set up for the user to navigate through the database using the PI field."

    The GrantID should be the main point of navigation. Your main Grant form is keyed on GrantID. If you need to show the PI info, that should be shown in a subform linked on GrantID. You should be able to navigate through the various PI records in the subform and edit each.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2022-08-08T14:14:34+00:00

    As I work my way through the new multi-table way, I noticed a bug with the working table. It does everything as intended BUT I have it set up for the user to navigate through the database using the PI field. I just noticed that if the PI has multiple grants, it will only show or allow editing of the second grant. I'm hoping this won't be a problem w/ the multi-table version but I haven't got that up and running yet. Thx again for all your help!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2022-08-08T13:46:14+00:00

    omg that makes so much sense

    Was this answer helpful?

    0 comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2022-08-05T13:53:33+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?

    Couple of things. I think we have discussed this before. Using a multi-table query as the Recordsource for a form is NOT recommended. This is what you are doing when you add fields from multiple tables to a form. As soon as you drag a field unto a form, Access creates a SQL statement that joins the multiple tables. The way to work with multiple tables on a single form is to use subforms. If the data is in a 1:many relationship, then you want to use a subform that displays multiple rows (either a datasheet or Continuous form). If the data is in a 1:1 relation, you can use a subform, but hide the borders, nav bar, etc. So that those controls appear as part of the main form.

    As for the combo boxes. If comboboxes automatically appear when you drag a field to a form, it means that you have defined that field as a lookup field in the table. This is also NOT recommended and for the reason you are having an issue with it. Lookup fields in tables mask the data that is actually stored in the table. In your case, what's being stored is the Foreign Key value, not the descriptive Value. So, for example, if you have a Sponsors table, Each sponsor has an Autonumber PK. So if you select NIH, it might have a PK of 2. And what is stored is the 2, not the name, Lookups should be done on forms using list controls like comboboxes. Comboboxes have a Column widths property. So the RowSource of the combo would include the SponsorID and the Sponsor name. But you set the Column widths so the first column has a zero width. The user then sees only the sponsor name, but when they select the name the ID is stored. If you use the Combobox wizard to add a combo to a form, it, by default, hides that key column.

    Was this answer helpful?

    0 comments