Access 2007- Find a Record Command Button on Form Doesn't Work

[aswgawlag]

Hello everyone.

I am new to Microsoft Access and have been creating a database to store records, add new records, update records, delete records, search records, create reports from records, etc.

With that said, I have little experience in this process and have been teaching myself as I go. However, I am now stuck because I created an Update Record Form to update existing records that are stored in a table with a find record button that no longer works.

When I first created the Find Record Command Button it worked beautifully, just as intended, which was strange because I had not even created a code for it. I have spend HOURS researching and trial and error trying to get this button to work again by trying different VBA codes and no luck. Twice, the find record button actually worked and when I exited the form after saving my changes, and went back in and tried it, it stopped working again. I don't understand.

I need to create a VBA code that allows me to find a record based upon any of 13 fields. For instance, I need for the button to work if someone wanted to search according to File Type, or Owner, or Document Number, and so forth.

Can someone please help?!

Thank you.

[patjones]

Well, I think in order for us to help we need to know how the button is currently programmed. What exactly is it executing when you press it? We can use that as a starting point and go from there.

Also, for the purposes of making this simple, we'll pretend that you are searching on one field. If we can fix your problem for the one field case, then the others should fall into place.

[aswgawlag]

Hi Pat. Thank you so much for your response. I am very new to Access, so I am going to do my best to answer your question to the best of my ability.

O.k. so I have a "FormUpdateCMRe cord", which consists of some text boxes and combo boxes. The combo boxes are queried to my "tblCMRecor ds", which includes all fields for the records in the one table. For instance, the table includes the "Document Number", "File Type", "Revision", "Title", "Owner", "Library", "Status", "Funding Source", "Charge Number", "Project", "Manufactur er", "Manufactur er Part Number", and "Comments". These are all of the fields in my table that store the data I need to be able to search for.

I used the command button wizard and added a button to the bottom of my "Footer" section of my form. All of the fields listed above are formatted into text boxes and combo boxes in the "Detail" section of my form. The title "Update Configuration Record" is located in the "Header" section of my form.

I followed the command button prompts and selected the "Record Navigation" category and "Find Record" action. I then named my button "Find and Update Record."

In addition, I added an "Exit to CM Menu" command button that works as intended.

I want to be able to press the "Find and Update Record" command button and have it search for a record according to "Document Number" for instance, which begins at 10001.

I tried to be as inclusive as knowingly possible here and give you as much information as I can. If I am not answering your question, please let me know and I will do my best to try and answer it again.

Thank you so much for your response and I hope this helps!

[aswgawlag]

And actually, the command button's Name is "btnUpdateRecor d" and the Caption is "Find and Update Record."

[aswgawlag]

Also, oddly enough, when I create a new test form with the same format as the form I am currently working on, the "Find and Update Record" comman button works as did it originally when I first created my current form and added the command button. However, I hadn't written a VBA code for the test form.

[aswgawlag]

I'm sure it would also be important for you to know that when I created a new Find Record command button on my form and attempted to search by inputting "10001" in the "Document Number" text box, a message pops up and states, "NGPA finished searching the records. The search item was not found."

[patjones]

OK, you did give me the information that I was looking for, which is that the command button is connected to something called a macro.

To give you a clearer picture of what's going on behind the scenes, open the form in design view, click on the command button, and in the properties sheet click the Event tab. The first event (On Click) will say "[Embedded Macro]" and have an "..." next to it. Click the "..." and it will show you what is happening when you click the button.

Essentially, a macro is a series of commands that are run when the button is clicked. In particular, you'll see the Find command, which causes Access to raise the Find dialog that you type your search value into.

I usually don't advocate the use of macros. Past versions of Access had security issues with them (I don't know about 2010). They are also used to accomplish simple tasks, whereas more complex tasks would require VBA.

In your situation, since you have one form connected to one table - a pretty straightforward arrangement - I can't really object to the use of the macro. If your new form works the way you want it to, then go with it. Why it is not finding "10001" is a question I can't answer off the top of my head though. If you're comfortable with the explanation that I've given of how the button is working, we can look at that in greater detail.

Pat

[aswgawlag]

O.k. I do understand there is a difference between a macro and a vba code. However, i've been using vba coding unless the button automatically executes as a macro, but I have not been going in manipulating the macro event in any way.

I was able to go and change a field in my properties pane and then my find button worked again! I believe I changed the data entry from yes to no and that did the trick.

However, I just want the users to be able to search with this form and not make any changes to my table. Do you have a suggestion of how to go about this? Should I change the options in the properties pane? I don't really see much difference when I do; however, if I change allow additions to no, my find button no longer works again. As of now, I am able to search for records based upon any field I'd like; however, I have not written any code for the button, yet it still seems to work. The only issue I am having now is that whatever I search for, like if I enter 10001 in the Document Number field, it pulls up the records with that Document Number, yet when I exit and open my table to see if anything was changed, the searched data...10001 for instance...is now entered as a new record in my table. I do not want this to happen. I want my table to remain untouched. I was hoping maybe you would have a suggestion of how to stop this?

Thank you very much for your feedback so far. :)

[patjones]

Hmm, okay. I was pretty sure that you weren't using VBA code, because you said that you created the button with the wizard and it just worked. When one creates a button with the wizard, a macro is generated.

Can you post the VBA code? Also, what are the users typing the search value into (e.g. a text box on the form, a dialog that pops up, etc.)?

Pat

[NeoPa]

In case it helps, and I'm not planning on complicating matters at this stage by giving instructions when Pat is already handling things, there are a couple of articles available that you might find helpful to do with form filtering (which is what people often mean when they talk about finding, or going to, records). If they're useful then great, otherwise ignore them :

Example Filtering on a Form.
Cascaded Form Filtering.

[aswgawlag]

Pat,

These are the only VBA codes I have within my "FormSearchCMRe cord":

Private Sub btnCloseForm_Click()
    Dim iResponse As Integer

    iResponse = MsgBox("Are you sure?", vbQuestion + vbYesNo, "Cancel Changes?")
    If (iResponse = vbYes) Then
        Me.Undo
        'Cancel = True
        DoCmd.Close
    End If

    'Me.Undo
    'Cancel = True

    'If DoCmd.RunCommand <> Null Then
    '    DoCmd.RunCommand SetWarnings = False
    '    DoCmd.RunCommand acCmdDeleteRecord
    '    DoCmd.RunCommand SetWarnings = True
    'End If
    'DoCmd.Close
End Sub

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
    DoCmd.OpenForm "FormSearchCMRecord", , , , acFormReadOnly
End Sub

I didn't write a VBA code for the "btnSearchRecor d" and it is working as intented now. I also added that last code, which has locked my records, so they can not be changed in any. I just didn't want users to be able to manipulate the records, especially unknowingly. The purpose of this is to better control our data. The other forms, which allow for additions, deletions, and updates are all restricted with a password, yet all linked to one table that stores all the data for a given record.

Also, to answer your other question: The form consists of 6 text boxes being the data in these fields is always changing to something different, so if I used a combo box, it would be ridiculously long. However, if they enter an invalid "Document Number", which does have a text box for instance, then when they search it will obviously come up with no results. Additionally, I have included 8 combo boxes being these fields include data that has less variability. The form is set up, so the users may search by the document number, or the owner, or the file type, and so forth...just depending on what exactly they need to know. If additions are made to the fields that have combo boxes, then the choices within the combo box are automatically updated as well.

I hope I am explaining this clear enough.

Thanks again for all of your feedback. It is much appreciated.

[aswgawlag]

NeoPa,

I will definitely take a look. I have skimmed over some things involving filtering, yet was still unsure about the difference between using a filter and a find record button...if there is a difference, but from my understanding it seemed there was. I briefly tried using a filter even though I was unsure if that was what I needed or not.

At this point, I just created a find record button, which is what the add button wizard called the function.

Thank you for the articles. The information is appreciated, especially since I am still very much a beginner and am doing my best to learn and understand how Access functions.

[NeoPa]

You're welcome.

The difference between them is that a filter shows only records that match any specific criteria, whereas a find takes you to such a record. If you consider a table of a hundred records where ten match a specific set of criteria then filtering shows only those ten records, whereas a find simply takes you to the first record it finds to match the criteria. It still shows all one hundred records.

[patjones]

Well, there is nothing in your code that would perform a find on the button click event, so it is certainly being accomplished via a macro. You do get a Find dialog box when you click the button, yes? And this functionality is working correctly, yes?

Pat

[aswgawlag]

Pat,

Yes I do get a find dialog box whwn I click the button and the functionality is working correctly. Thank you.

Since the weekend, I have opened my database and everything except for my Delete Record Form seems to be working. When I click on my btnDeleteRecord after finding the record I want to delete, a message box pops up stating something along the lines of...You are about to delete (1) record. If you perform this function, you will not be able to undo this. Are you sure you sure you want to continue? When I select yes to delete and check in my table, the record has not been deleted. Any idea on what may have happened or a way to prevent this from happening again?

Thank you.