Display single record or duplicate records

[mcervenka1]

Hello NeoPA. I think I have a brainteaser for you. When I use the form (Update Record) with the above VPA, it works good but it also ask for input through a parameter query popup box. I have researched and found the parameter query is based on a embedded macro located in another form (Inquiry Record). I believe I caused this when I copied the Inquiry Record form with the embedded macro and renamed it Update Record. Can that be the reason??? If so, how do I get rid of the embedded macro in the Update Record form.

[mcervenka1]

Correction. when I copied the form called "Inquiry Record" with the embedded macro and renamed it "Update Record" .....

[mcervenka1]

oh yes to make it more interesting ... in the Update Record command button properties "On Click" it only shows event proceedings (You VBA code) it does not show the embedded macro coding.

[NeoPa]

I'm not sure I can answer that. It would require some understanding of what's in the macro that you have somehow managed to get into your Form. If you need this macro and also need help with it then I suggest you post a question thread about it. I don't believe it fits in this one.

If you don't need it then simply remove it. I can't help you decide as I have little understanding of what it is or why you have it in the first place.

I'm sure someone will be able to help if you post a new question. I'll probably see it but have very limited experience with macros in Access so may not be able to be of much use. Nevertheless, there are many here who can and will offer help.

[mcervenka1]

Hello NeoPa
PLease look at the attachment containing the VBA that you provided me and advised if I have everything written correctly. When I open form I am getting the error message "Complier Error, expect end of statement". Also while researching I looked at several VBA coding and they all had the "On Error Goto instructions" after the DIM statements? Does it matter where the On Error Goto statement is placed?

[twinnyfo]

mcervenka1,

Please double check that your Text Box is named "txt.Search ". If not, then remove the dot from that Sub's Name. If so, rename it to "txtSearch" and remove the dot from the subs name.

See if that will help, as that dot appears to be the cause of the problem in your error.

[NeoPa]

Good spot Twinny. I certainly hadn't intended to include the dot (.) and that would certainly cause it to fail.

TBF my original post did have a Government Health Warning on it in case of any mistakes, as I wasn't able to test it.

[mcervenka1]

OBJECTS In Database and VBA

NeoPA... I do not know it this is allowed, if not I apologize. Attached is screen shots of records in database, query for INQUIRY RECORD, INQUIRY RECORD FORM, and Properties and VBA coding. This is the same type of documents used in UPDATE RECORD. You asked to see some information when you were trying to help earlier. I made changes to txtSearch. Now when I enter the receiptnumber or ANumber or Serial number in the search field, the search function does not work. Happy to say I do not get a compile error... Are you able to assist? If not, I understand and thank you for the time you have spent trying to assist me

[NeoPa]

MCervenka:
Are you able to assist?

Not easily. I see nothing which indicates what you're trying to search for and I don't see what your .Filter property is set to.

Please add, just before the first End With line, a line that says Debug.Print .Filter; then post the results of that line.

Certainly I'd like to help if I can, but it's very difficult when I don't have the information I require available. If I were doing it for myself I'd follow a number of steps - each one dependent on the results of the last - to take me slowly but surely towards an understanding of what's wrong. That's very difficult to manage via a forum. Let's see what we can do anyway.

NB. This is perfectly acceptable usage of the forum. You're continuing to explore and work on a single issue. That's fine - as long as you continue with the same topic.

[mcervenka1]

If you are talking about Form.Filter Property? If not where can I find the information for you

Filter......(no Setting)
Filter on Load ...NO.....( has NO for a setting)

also inserted the Debug.Print .Filter; are able to enter the receipt number but the search function is not working. No change window after click on the Search button. No errors when I ran the debug. Sent snap shot of screen.

[NeoPa]

Before or after you run that code type Ctrl-G from anywhere in Access and it will go to (and make visible, if not already) the Immediate pane. Please copy the contents of that after the code has run once and we'll see what it says.

PS. How you've updated the code is correct. The indenting is wrong but that won't affect what we're looking at here so ignore that - at least for now.

[mcervenka1]

Please see attachment

[twinnyfo]

After you execute the search, there should be some text, indicating the contents of the Filter, listed just below the "Immediate" . Either the Filter is completely empty, or you provided this screenshot before you ran the code.

[mcervenka1]

see attachment. used SRC3333333333 in the search field. There is a record in the table with this receipt number. No search results were given as seen on the form screen snapshot. Used ctrl-g after and provided snapshot of that. I understand about filter being empty. Is it empty because like I said, when I entered the receipt number and push the SEARCH button no resolts were returned.

[twinnyfo]

First, it is not necessary to provide screenshots of the immediate window. Simply copy and paste the results into your post. This is much easier for us to manage.

Second, I am not certain your code is even firing when you enter search criteria. At an absolute minimum, the Immeidate Window should show this:
[ReceiptNumber] Like '') OR ([ANumber] Like '') OR ([SerialNumber] Like '') and that assumes the search criteria is blank.

Again, are you certain that this VBA module is attached to the form you are working on, that the text box is named txtSearch and that the procedure is properly associated with this text box?