Filter a Form with Repeating Data (1 Viewer)

sxschech · Apr 26, 2019

Was handed a project to add data to an excel sheet. Because data were concatenated and some would keep and some would change and there were some rules about which rows to use, I thought it would be a simple matter to bring into Access and use queries and forms to take care of the tedious parts. It took longer than I thought to set up, and am a bit burned out so it may be a simple solution that I'm not seeing. I found AutoFillNewRecord from the microsoft site.
https://support.microsoft.com/en-us...-data-from-a-previous-record-automatically-in

Added that to my module and form as instructed. It worked. Unfortunately, when I tried to use it with the combobox to find record, I get error

The expression On Current you entered as the event property setting produced the following error: Type mismatch.

I tried stepping through the code, but it didn't show me the code, went straight to the error. I couldn't see where the problem is. In the meantime, I have been manually setting the filter by clicking in a field cell using datasheet view and then clicking the Selection under Sort & Filter ribbon. Once I have finished adding records, I toggle filter to turn off, then have to remember where I was in order to locate the next record to filter. Tedious, but works.

theDBguy · Apr 26, 2019

Hi. Is there any way you could share a small copy of your db with test data?

Micron · Apr 26, 2019

If you used a combo box, chances are it has more than one column and the one you have bound to the table or query isn't the one (or one of the ones) you're looking at. Thus the expected data type you expect to pass isn't suitable. The error message is like a directional sign on the highway, which we all know, tells us what we need to get where we're going. I think you could learn more by researching error meanings and diving in to validate your design against their meaning, but that's just my opinion.

sxschech · Apr 26, 2019

Thanks for taking a look. Here is a copy. Did a quick copy, so pretty generic, regarding naming. The form I'm currently using is Form2. To use it for additional records, I copied all the relevant data from qryFilter into it. However, for the ideal situation, I would only add new data when selected from combobox. Which would take data from qryFilter and add to tblThreads. I put in 2 records to use.

qryExport concatenates the data to be copied back to excel when assignment complete

Form2: (Form I'm using currently)
I already added the additional data for Bridge OCS data. So next step is to add new data for Submit OCS Data. When you click in the cell under Mail Number for a new record, it brings down the Activity Name and RN. Then I would fill in the Mail Number, Status and Finish Date manually.

The next two forms is where you can see the error messages.

Form3:
Was another attempt at doing a form/subform from scratch

FrmSpecs:
Was a form I copied from one of my other dbs as a head start, so not finished design/code wise for this revised purpose.

If you need further explanation let me know.

theDBguy · Apr 26, 2019

Hi. In Form2, try changing the Current Event to this:

Code:

=AutoFillNewRecord([Form])

Hope it helps...

sxschech · Apr 26, 2019

Thanks, the suggestion seems to work. Was wondering about difference between Form and specifying the specific form name and when to know which to use?

theDBguy · Apr 26, 2019

sxschech said:
Thanks, the suggestion seems to work. Was wondering about difference between Form and specifying the specific form name and when to know which to use?

When you use Forms!..., you are asking Access to check the Forms collection. This is fine for all open forms. However, a subform will not be part of this collection, so the reference fails in your case.

sxschech · Apr 26, 2019

So in future, if I get an error specifying the form name, I should try just using Form and see if that works?

theDBguy · Apr 26, 2019

sxschech said:
So in future, if I get an error specifying the form name, I should try just using Form and see if that works?

Not really. This worked for you because you were using a function that requires a form object for an argument. If you need a reference to a form control, you can still use the Forms! syntax but just need to adjust it for subforms. For example:

Code:

Forms!MainFormName.SubformControlName.Form!ControlOnSubformName

sxschech · Apr 26, 2019

Thanks, not sure if supposed to thank both posts 5 and 9 or original, so did #5.

theDBguy · Apr 26, 2019

sxschech said:
Thanks, not sure if supposed to thank both posts 5 and 9 or original, so did #5.

Hi. You're welcome. Glad we could help. Good luck with your project.

Filter a Form with Repeating Data (1 Viewer)

sxschech

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

sxschech

Registered User.

Attachments

theDBguy

I’m here to help

sxschech

Registered User.

theDBguy

I’m here to help

sxschech

Registered User.

theDBguy

I’m here to help

sxschech

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread