OpenForm Cmd not working today...

pswilliams0

Registered User.
Local time
Today, 02:12
Joined
Apr 19, 2012
Messages
20
Hi All,
I'm a bit of a novice with Access, however this forum has typically had an answer when I'm completely befuddled...
So, here's a description of my DB:

I have a database that has a form used as a command bar. On that command bar is a text box. The value of that text box is used as a search field with a button. The searchable field has no duplicates, so the VBA code is written to match the number from the text box to a specific field on the form it opens. Like this:

Private Sub Command40_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[EN Number] ='" & Me.Text41.Value & "'"
stDocName = "EventList_Justified"

DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria, , acWindowNormal
DoCmd.MoveSize 0, 0, 12000, 11000

Exit_Command40_Click:
Exit Sub

Err_Command40_Click:
MsgBox Err.Description
Resume Exit_Command40_Click
End Sub


In this case, the text that is entered for the search field is typically a 9-digit number, sometimes with a Letter at the end. Therefore, the text box and form field is classified as "text" instead of "number".

So, the problem is this:
as of this morning, this search function will open the form, but not match the value to and open the form to the correct record. Instead, it opens the form and lists "#Name" in all the fields. The table shows the value exists, and I can access it by navigating through all the form entries (instead of the search filter).

This has been working for many months with no issues. Then this morning it resulted in this. I am the only person able to modify the backend (access restrictions) and have made no modifications. However, this issue has presented itself on all users' frontends.

I am at a complete loss as to where to even start to investigate. Your help is greatly appreciated!
 
did you change "EN Number" field name in the BE mistakenly?
 
As stated, no changes to the backend have been made for months. I am the only person with access to do so.

also, I checked the field names to make sure and they are correct.... :)
 
First thing to do is to put a breakpoint at the start of the procedure and see what
Me.Text41.Value
is returning.

Second thing is to open the "EventList_Justified" form in design view and look in the FILTER property and see if anything is listed there. Sometimes, it can have a filter stuck there which will cause a problem like what you have. Those typically happen if you have done a test and then do some changes to the form and save it without clearing the filter. Or using acSaveYes in a DoCmd.Close command.
 
Hi Bob,
Thanks for your help. So, I don't know how to set the break point. I understand how it works, I just don't know how to do it. However, I know how to set msgbox functions. So, I did the following:
stLinkCriteria = "[EN Number] ='" & Me.Text41.Value & "'"
MsgBox Me.Text41.Value, vbCritical
stDocName = "EventList_Justified"

The number that displayed came back as the value in the text box -- as expected. However, the form still came up as "#Name?" in all fields.

Second - the form did have a filter, however it was the filter I had typed in the textbox on the command bar (and confirmed with the msgbox). I presume that I should have expected that, correct?

Is there something else I'm missing? THanks for all your help.
 
Is your form "EventList_Justified" is underlined to the specified Query/Table?

Suggestions:
1: Try to compact and repair database.
2: Check if your Text41 is named correctly Text41
3: OR just re-create the Text41 and reference the same name to your code
4: Insert Break-points in VBA editor, click on command button and step through line by line by pressing F8 key, notice the Text41 value
 
Is your form "EventList_Justified" is underlined to the specified Query/Table?

Suggestions:
1: Try to compact and repair database. Done. No improvement.
2: Check if your Text41 is named correctly Text41 -- yes, it is.
3: OR just re-create the Text41 and reference the same name to your code -- I did this as well, to no improvement.
4: Insert Break-points in VBA editor, click on command button and step through line by line by pressing F8 key, notice the Text41 value-- Done, the values are returning correctly, see my previous post.

As I stated in previous posts, there have been no code changes. (Or any known changes to anything but data) The data isn't corrupted if it is all still there and can be searched via a different field on the form, right? However, when searching, only the autonumber will result in the desired filter, any other search results in the current result of "#Name?".

Thank you all for the ideas, keep 'em comin!
 
Ok, I found the issue! A single entry had become corrupted - or at least that's what I think - it read '#Deleted' in all fields. Because of that, the indexing of the searchable fields was screwed up and just returned errors upon querying. Upon compacting and repairing the backend table, everything works like a dream.

Thanks for your help in trying to figure this out!
 
Ok, I found the issue! A single entry had become corrupted - or at least that's what I think - it read '#Deleted' in all fields. Because of that, the indexing of the searchable fields was screwed up and just returned errors upon querying. Upon compacting and repairing the backend table, everything works like a dream.

Thanks for your help in trying to figure this out!

Good you sorted it out, the deleted record should clean up automatically whenever you open the table again OR there should be Re-Query action after every deletion on the form. AND again WHY deletion is allowed by users for any record on distributed database?
 
Khalid, lets not jump ahead now. Nobody deleted a record, rather a SINGLE record became corrupted which caused issues with the indexing of the Jet database. Once the backend was repaired, everything worked like a charm. Prior to this, any attempt to delete the corrupted record was not possible (presumably because it was corrupted! :)

Once again, thanks to everyone for the helpful ideas.
 
Great you figure it out. corrupted records are always a headache in Access, I fought with them many times, they effect queries a lot, effecting report printing, effecting data processing and cause network problems.
 

Users who are viewing this thread

Back
Top Bottom