Creating a Form with Multiple Prepopulated Text Boxes

[fortwrestler]

Hello,
First,
I am newer to Access, and have been learning on the go/web tips/youtube. I am familiar with programming language basics, and am starting to understand VBA.


I am working on a form which can populate multiple prepopulated text boxes.

Basics
I have a piece of equipment with multiple reasons that it can stop. I want to create a form which allows the user to input how many times it stopped for any given reason.

So far, I have only been able to create a form where the user has to select the reason from a combo box, then can enter the number of stops.

Ideally, I would want the form to look like this when opened

Reason 1 xxx
Reason 2 xxx
Reason 3 xxx
... xxx
Reason N xxx

Where "Reason 1...N" is prepopulated from a reasons table(40 entries) and "xxx" is where they would input the number.

I assume there is a macro to generate these boxes, but my searches so far have turned up empty.

Any help would be appreciated

 

[pbaldy]

Well, an append query can add those 40 items to another table. What's your table structure? I do something similar for data entry where I append all available rates into a daily activity table. The user can then just go down the list entering the quantity for each. At the end, I delete any that are still zero.

 

[fortwrestler]

I have one table that has equipment(8 unique equipment - long term I will do this for all, but for now I am just trying to figure out how to do for one)

Table 1: equipments(pk)
Table 2 : equipment(just one piece of equipment), Reason(pk)
Table 3 : ID(pk), equipment, Reason, Entry Date, Num of Stops

So Table 3 is the table I want to populate with (user input) Entry Date and Number of Stops, while everything else is defined.

 

[pbaldy]

I'd have a form where the user entered the date, and the piece of equipment. Create a query based on the table with the 40 reasons that returns those 40, then in the field row of a new column, add a reference to the form for both the date and equipment (you can right-click and select build). That query should return the 40 reasons plus the date and equipment from the form. Turn that query into an append query that populates table 3, and run that append query from a button on the form.

 

[fortwrestler]

I completed these tasks. At what step can they enter the number of reasons now? Do i need to create another form?

 

[pbaldy]

I would either have another form or a subform on that same form that displayed table 3 data, restricted to the equip/date specified. Either would be in continuous or datasheet view so multiple records were displayed.

 

[fortwrestler]

Thank you.... Much much appreciated. Been banging my head on desk all day about this issue.

 

[pbaldy]

Happy to help, and welcome to the site by the way!

 

[fortwrestler]

Sorry to reopen this, but i'm back to working on the database.

So, I added a subform(which shows the appended query) to the form where I put in the date and equipment. The subform is not populating once the append is done (even after requery macro).

The form itself does work.

 

[jdraw]

Can you post a copy of your database-- preferably in zip format?

Very thorough youtube tutorials on database and design start here.

 

[fortwrestler]

Here's the file....

The tables I'm working with are

EQ5A Reason Table
EQ5A Stop Table Info

Queries:
EQ5A Reasons Table Query(Append)

Form:
EQ5A Data Entry Form
Filler Select Stop Form

The Filler Select Stop Form is the form I am working on, where the Date Entry form is the 'subform'

 

[pbaldy]

Well, the subform is based on the table, so it won't filter to the criteria specified. I'd base it on this:

SELECT [EQ5A Stop Table Info].*
FROM [EQ5A Stop Table Info]
WHERE ((([EQ5A Stop Table Info].Filler)=[Forms]![Filler Select Stop Form]![Filler]) AND (([EQ5A Stop Table Info].EntryDate)=[Forms]![Filler Select Stop Form]![EntryDate]));

You don't want the subform with its Data Entry property equal to Yes. Also you don't want the master/child links filled out, unless you go a different way.

 

[fortwrestler]

Do I need to add a separate query with that code?. It looks just like the append query I have

 

[pbaldy]

Well, it looks similar because of the criteria. The append query selects from the reasons table rather than the stop table, and obviously appends rather than just selecting. It can either be a stand-alone query or put directly into the record source property of the subform.

 

[fortwrestler]

So. I need to change my 'subform' to pull from the query you submitted versus mine?

 

[pbaldy]

Unless I looked at the wrong form, the subform was bound directly to the table, not a query.

 

[fortwrestler]

You were correct. I meant should I keep it bound to the table or move it to the query....

Sorry for being slow with this. Kinda learning as I go.

 

[pbaldy]

Presumably the table is going to contain multiple fillers and multiple dates. You only want to display a single filler/date combination, so I'd use the query.

 

[fortwrestler]

So,
I've gotten that part figured out (and with multiple fillers/reasons...yay).

Is there any good way to prevent duplicate "appends".
I've looked around and noticed that setting the fields to unique would do so, but thats not applicable in my case.

Basically, a 'duplicate' would occur if the Line, Entry Date, and Reason are all the same, given that a line would only have a given reason one time per day.

EDIT: Figured this out...........

 

[pbaldy]

Glad you got it sorted. I go to lunch and problems both come up and get solved.