Refresh button to update subform to criteria controls getting Macro Error 2046

[mikeone610]

HI. So I had everything working smoothly last night no errors or problems so I saved and shut down my computer. I then woke up to a nightmare. My once full functional database won't allow me to run a query off my form.



I have setup a ton of controls on the top of a form and then I have a "Refresh Form Data" button that I used to refresh a Subform that displayed the query below. No all of sudden it won't work, I get an error window that says this:



And then I hit Ok and the next window is this:



I even rebuilt the whole database AGAIN and it was all working fine until I put in the subform that displays the query which the forms criteria is linked to. I can still push the other button which opens the query in a new window but I can not use the "Refresh Form Data" button to update the subform. I am in a real bind here because I was suppose to show this thing off tonight and now it is not usable.

[rpeare]

A good reason to hate macros and why most programmers use code as opposed to macros.

The first error message is telling you that someone else may be in the database or that perhaps control of that database was not released the last time the database was exited OR possibly that you have done your programming in one version of access but you are now trying to open it in a different version. I suspect it's the former rather than the latter if you created the database from scratch.

[mikeone610]

HI! Thank you for replying. Yeah I have only used Access 2010 and I made it from scratch besides the Tables. Any thoughts on what to do? Is there any way I can clear out it thinking someone else is using it or the control problem?

[rpeare]

There are a couple of things you can do

1. Make a copy of the database in a different location, I think you can copy the database without being the exclusive user
2. Go to your network admin and ask them to tell you who has the file open if they tell you it's just you (and you don't have it open or someone else does) you can ask them to terminate the connection if it's a bad one

[mikeone610]

Ok I will try. I am the Admin. Like this is on my home laptop, only one user account.

[mikeone610]

Dam. I was able to copy it to a new location but I still get the same error message when I try to refresh the form data button. That is the correct command to use right? Like in order to update a subform which is displaying the query on the form you want to use the refresh form data button?

Also is there maybe a way to try an circumvent this by adding a macro that would refresh the table in the subform when a button is clicked? I am trying to Google how to do this I am not very good with Macros.

Man the clock is ticking I am getting nervous.

[rpeare]

you use refresh when you want to refresh the contents of a database (for instance if you add an item to, say, a list of customers, but the new customer does not appear in a combo box, you would put in a me.refresh then requery the combo box). If you are just after an updated set of data you'd use the requery function like

List_001.requery

requery also works for refreshing the contents of a subform

[mikeone610]

How do I get to that? The:

If you are just after an updated set of data you'd use the requery function like

List_001.requery


​Part?

[rpeare]

Oh hell I forgot you were using a macro and not code, I'd suggest converting your macro to code and looking for where it says 'refresh' and possibly changing it to requery.

It'll be nearly impossible for me to tell you what to do with your macro without a dummy copy of your database (I don't use macros for this among many other reasons)

so whatever your event is, if your macro is currently running on an AFTER UPDATE event or something like that if the only thing you're doing is running that macro and the only thing in that macro is currently your refresh command you can requery the object you want instead of doing a full database refresh

[mikeone610]

I'm sorry I am not really using a Macro, I just kinda suggested that as a work around. I haven't really had to add any VBA the whole database everything is done through the simple input (if that is what you call it) from the main Access program.

[rpeare]

I don't know your database, you are going to have to figure out where it goes and what you want to requery unless you can provide a sample of your database.

Whenever you update a dataset, change the results of a subform or listbox or combo box that's when you would want to put the

<objectname>.requery statement

where <objectname> can be a subform, listbox or combo box object name.

If you do upload a sample I can not read anything higher than 2003 at the moment.

[mikeone610]

This is what I have in the VBA right now:

Private Sub RunSearch_Click()
Forms!SubformNew.Refresh
End Sub

I am new at this. I thought this mean when I click the RunSearch button it would refresh the Subform called SubformNew but it now does not do anything including giving me the error box at the moment. Is that code way off? I keep googling "Access vba button" and "Access vba button requery" but there is not much out there, which i surprising.

"RunSearch" is the name of the button on the form and "SubformNew" is the name of the subform displaying the query results below the button and criteria. Like I said it works a 100% when I use my old button which runs the query in a new window but it just won't work in the form and subform. I have like an hour before I am going to let some folks down.

[mikeone610]

WHOA!!! Solved it!

In case anyone else has this problem and finds there way here. What I did was delete the original button that gave me the error and then I added a new blank button. In the buttons properties in the On Click I added the following:

=[SubformNew].[Requery]

so

=[name of your subform].[Requery]

and like magic it worked!

[qa12dx]

i think in ur macro u r using runcommand-refresh, just use requery instead.

if u wanna requery a subform make sure u refference the subform name else it will requery the form and display the first record.