By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

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

P: 16
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.
Nov 15 '11 #1

✓ answered by 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

Share this Question
Share on Google+
19 Replies


patjones
Expert 100+
P: 931
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.
Nov 15 '11 #2

P: 16
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 "FormUpdateCMRecord", which consists of some text boxes and combo boxes. The combo boxes are queried to my "tblCMRecords", 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", "Manufacturer", "Manufacturer 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!
Nov 15 '11 #3

P: 16
And actually, the command button's Name is "btnUpdateRecord" and the Caption is "Find and Update Record."
Nov 15 '11 #4

P: 16
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.
Nov 15 '11 #5

P: 16
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."
Nov 15 '11 #6

patjones
Expert 100+
P: 931
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
Nov 15 '11 #7

P: 16
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. :)
Nov 16 '11 #8

patjones
Expert 100+
P: 931
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
Nov 16 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 17 '11 #10

P: 16
Pat,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCloseForm_Click()
  2.     Dim iResponse As Integer
  3.  
  4.     iResponse = MsgBox("Are you sure?", vbQuestion + vbYesNo, "Cancel Changes?")
  5.     If (iResponse = vbYes) Then
  6.         Me.Undo
  7.         'Cancel = True
  8.         DoCmd.Close
  9.     End If
  10.  
  11.     'Me.Undo
  12.     'Cancel = True
  13.  
  14.     'If DoCmd.RunCommand <> Null Then
  15.     '    DoCmd.RunCommand SetWarnings = False
  16.     '    DoCmd.RunCommand acCmdDeleteRecord
  17.     '    DoCmd.RunCommand SetWarnings = True
  18.     'End If
  19.     'DoCmd.Close
  20. End Sub
  21.  
  22. Private Sub Form_Load()
  23.     DoCmd.GoToRecord , , acNewRec
  24.     DoCmd.OpenForm "FormSearchCMRecord", , , , acFormReadOnly
  25. End Sub
I didn't write a VBA code for the "btnSearchRecord" 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.
Nov 17 '11 #11

P: 16
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.
Nov 17 '11 #12

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 17 '11 #13

patjones
Expert 100+
P: 931
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
Nov 18 '11 #14

P: 16
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.
Nov 21 '11 #15

NeoPa
Expert Mod 15k+
P: 31,709
I can only guess you are somehow dealing with a ReadOnly table for some reason. Are you able to add a new record or update an existing one using the same form?
Nov 21 '11 #16

P: 16
Well, I did change my Search Configuration Record Form to Read-Only Friday, so that may have something to do with it.

I was asked to create a seperate form for each function to help control documents. So, to answer your question, I cannot add or update a record using the Delete Configuration Record Form. I have a seperate Add Configuration Record Form to add records and an Update Configuration Record Form to update records. Those are restricted with passwords. The Search Configuration Record Form is open to any user who has access to oue network, yet is set to Read-Only to prevent any changes to the master table of records.
Nov 21 '11 #17

NeoPa
Expert Mod 15k+
P: 31,709
We appear to be talking at cross-purposes. I was referring to the state of the record source that the form is bound to. You appear to be referring to the form itself (though how that would make sense is beyond me). Perhaps you could clarify.
Nov 21 '11 #18

P: 16
O.k. If my table is set to read-only I did not intentionally do this. How would I go about finding that out?

Now my btnDeleteRecord is working just fine. I find that this continues to happen from time to time, yet I am unsure of why. Sometimes records were added to my table when I would merely search for the record, yet now that I have set my search form to read-only that has stopped happening. This would also happen if I would go to add a record using my Add Configuration Record Form and when I would cancel and close out to my CM Menu, the record would still be added.

And just to clarify that...I was testing the database to see if someone were to go to add a record and change their mind or need to close out without adding the record, I wanted the user to be able to click on the btnCloseForm and the record be canceled and not added.

And now, when I use my Search Configuration Record Form and search for Document Number 10001, for example, it will add a blank 10001 record in my table for some reason, yet was not doing this Friday when I left work.
Nov 21 '11 #19

NeoPa
Expert Mod 15k+
P: 31,709
You're still talking about setting a form to Read/Only without explaining what you mean by that. I see no ReadOnly property available for forms so as far as I can see you're still making no sense (hence my request for clarification). I will try to deal with your points notwithstanding that, but I may not be able to in the circumstances.
  1. If it were Read Only I would not expect it ever to allow additions, updates or deletions. I expect this means that the record source is not the issue.
  2. This paragraph only tells me that you have a bunch of confusions and code which is not working as you seem to think it is. Having no information on this other than your oblique allusions to it though, I cannot comment.
  3. This paragraph tells me nothing further.

As far as I can tell we're still in the same position we were in before (fundamentally), except I now suspect, assuming your comments are accurate and reliable, that the issue is not with the Record Source itself. As this was what I was after though, it does indicate that my idea was not a route to a solution.

Clearly you have other issues with your setup, but as we have no details of your setup we're in no position to help further as far as I can see. You should understand that it's your responsibility for ensuring the question makes sense and is supported by the relevant information. While I understand this is not always easy, it's not something anyone else can do for you (any more than I have already tried to prompt you anyway), so it's down to you.
Nov 21 '11 #20

Post your reply

Sign in to post your reply or Sign up for a free account.