Solved Autofill a field

[Abes]

Hi All - I am new to the forum (and looking forward to being part of it), also a novice as far as Access is concerned, so please bear with me.

I have looked at the responses to similar questions on this forum regarding auto-filling and don't find what I am looking for, hence this one.

Let me sketch it as best I can:
I have a parent form with a child form in it.
I have the parent table (tblEvents) and a child table (tblResults)
The parent table has an Autonumber field in it as the primary key (events_ID) and I have a field in the child table (results_ID) as the foreign key.

What I need to happen is:
When a User enters a new record in the "events" table the key field (events_ID) is allocated a unique number by way of the autonumber action. When the User now moves to start capturing multiple records in the child table (tblResults) I want the foreign key field (results_ID) to autofill so as to be the same as the value of the key field in the parent table.

This is simply so that the User does not have to manually type in the value in the child table. I would then want to prevent the user from editing the value in the child table foreign key field, bearing in mind that the field is simply a number field (Long Integer) and has to remain in sync with the parent table primary key field's value.

Any help will be appreciated and please keep in mind that if you are going to tell me what to do, you are gong to have to tell me how to do it as well

Thanks

 

[theDBguy]

Hi. Welcome to AWF!

There is nothing special you need to do to accomplish that. All you have to do is use a form/subform setup and set the autonumber field as the master linked field and the foreign key as the child linked field.

 

[mike60smart]

Hi Abes

When you say " primary key (events_ID) and I have a field in the child table (results_ID) as the foreign key."

The normal link would be Events_ID PK to Events_ID FK ?

 

[bob fitz]

Set the subforms "Link Master Field" property to events_ID and the "Child Master Field" property to results_ID

 

[Abes]

Wow! Thanks for the prompt responses. Just goes to show, one (that being me) tends to look for and expect something to be complicated when it is not. Still have a lot to learn about Access

 

[theDBguy]

Wow! Thanks for the prompt responses. Just goes to show, one (that being me) tends to look for and expect something to be complicated when it is not. Still have a lot to learn about Access

That's what we're here for. Good luck with your project.

 

[Pat Hartman]

Welcome aboard.

Even simpler, if you create relationships as you should, Access "KNOWS" what the Master/Child links should be and will automatically create them when you place the subform on the main form.

In a relational database, use Referential Integrity to help to ensure that only valid data can be added to tables. Make sure you understand how it works so you know when it is appropriate to use Cascade Delete. Cascade Update is never used when your primary keys are autonumbers since it is not possible to change an autonumber value so there would be nothing to cascade.

 

[HeadAgainstABrickWall2021]

Hi. Welcome to AWF!

There is nothing special you need to do to accomplish that. All you have to do is use a form/subform setup and set the autonumber field as the master linked field and the foreign key as the child linked field.

Hi theDBguy, just wondering how this works if you had to enter data at multiple different points in time? Are you trapped into having to track back to the original parent form and filling from there (e.g. if you had a customer who has been with your organisation for years), or if you have to fill in multiple versions of subforms at the same time (e.g. processing payment receipts for different customers)?

 

[theDBguy]

Hi theDBguy, just wondering how this works if you had to enter data at multiple different points in time? Are you trapped into having to track back to the original parent form and filling from there (e.g. if you had a customer who has been with your organisation for years), or if you have to fill in multiple versions of subforms at the same time (e.g. processing payment receipts for different customers)?

One approach is to use a Tab Control to place the many subforms on the same main form. For example, the main form might be bound to the Customers table, the first tab might contain the Orders subform, the second tab might contain the Invoices subform, the third tab might contain the Payments subform, etc.

 

[Pat Hartman]

I'm not sure I actually understand the question but I think you are asking if you can update related records from a different direction. The answer is yes. This is not normally done for 1-m relationships since the many side has no existence without the one-side. However a m-m relationship is frequently updated from either direction. You just need to create additional forms. For example, students and classes. You would have a student main form and a subform linked to the junction table that shows the classes info. Or you can have a class main table with a subform linked to the junction table that shows the student info.

 

[HeadAgainstABrickWall2021]

One approach is to use a Tab Control to place the many subforms on the same main form. For example, the main form might be bound to the Customers table, the first tab might contain the Orders subform, the second tab might contain the Invoices subform, the third tab might contain the Payments subform, etc.

My understanding was that if you tab subforms then they will all relate to the parent form? I.e. create Customer, then go to enter Case and this will relate directly to the Customer info you just entered? The data that will be entered in my database isn't linear (i.e. occurs on a 'when it comes up' basis not a structured data entry process) so I don't know that this would work...

 

[mike60smart]

My understanding was that if you tab subforms then they will all relate to the parent form? I.e. create Customer, then go to enter Case and this will relate directly to the Customer info you just entered? The data that will be entered in my database isn't linear (i.e. occurs on a 'when it comes up' basis not a structured data entry process) so I don't know that this would work...

Can you give us an example of the "when it comes up" process ??

 

[HeadAgainstABrickWall2021]

Can you give us an example of the "when it comes up" process ??

So there might be a management meeting with external stakeholders to discuss applications made for aid packages. These applications get discussed in no particular order but the topics of discussion and the outcomes need to be recorded. The admin assistant won't have time to go back, search for customer, then to their current referral, then add event and record for each individual person. They do have time to open the New Event form, type the first three letters of persons surname into combobox, and click the name from the dropdown.

 

[mike60smart]

As Pat has indicated you are talking about a M to M situation.

Many Meetings can Have Many Topics..

All you would need are specific Forms to deal with the specific process.

The typing of the first 3 letters of persons Surname into a Combobox is standard Search process in access.

 

[HeadAgainstABrickWall2021]

But one event can only have one management meeting decision outcome? I have got individual forms for each stage at the moment.

I watch some tutorials on subforms and try and get my head around it

 

[mike60smart]

But one event can only have one management meeting decision outcome? I have got individual forms for each stage at the moment.

I watch some tutorials on subforms and try and get my head around it

Can you upload your current database so we can see what your current structure is?

 

[HeadAgainstABrickWall2021]

I'm going to strip out identifying branding and references today and upload (subcontracted to gov so have to be very careful)

 

[Pat Hartman]

At least post your relationship diagram. Make sure we can see all the column names and the relationship lines.

 

[gemma-the-husky]

Hi theDBguy, just wondering how this works if you had to enter data at multiple different points in time? Are you trapped into having to track back to the original parent form and filling from there (e.g. if you had a customer who has been with your organisation for years), or if you have to fill in multiple versions of subforms at the same time (e.g. processing payment receipts for different customers)?

If I may comment.

You don't want multiple payment subforms, just one.

You have a payments procedure to allocate payments.

So you have a payment form.
That form lets you record the payment ID, date, payment amount, settlement discount amount, payment source, reference numbers, and importantly, customer account reference, for each payment, one record at a time, together with anything else you need. The clerk doing the allocation, the date/time the allocation was processed, and so on.

This form could be a single form, or a continuous form. Whichever you find easier to manage and work with. A lot of places would have a manual list of payments to be processed to control and manage this payment allocation. Tick them off as you do them, so you know they are all done. Now you can see that you aren't trapped into anything. All you are "trapped" into doing is making sure that each payment does get processed correctly, and ticked off. Maintain a manual AR (Sales Ledger) control record that records the total outstanding balances that the AR ledger should be showing, so you know the processes are being operated correctly. That's what I did and do.

When you select the customer account, your allocation subform then shows all the unallocated transactions for the selected account. You can then mark the payment allocation against the outstanding items, and when the payments you enter agree with the total of the payment plus the discount, you can update those payments. The paymentid gets recorded against the payment allocations. You could have processes within this subform that let you match all transactions within a date range. Whatever it takes to make the process easy for the users.

It's not about the form design. It's about the data management really.

You need these tables.

customer table
customer transactions table (1 customer = m customer transactions - invoices, credits, journal; adjustments, payments etc)
payments table (effectively tying in with your banking records - this represents the payment form mentioned above)
payment allocation table (1 payment table entry = m customer transactions)

And then your processes manipulate the data in these tables. Get the tables right, and the process is much easier. Still complex practically, but not logically. Complex practically because a process that makes it easy for clerical staff to match a large payment with a large number of transactions, some of which will only be part payments is a complicated thing to get right. It's easy when customers pay one bill at a time. It's hard when they pay hundreds or even thousands of items at a time. Maybe they send in a spreadsheet with the payment, and you can can use that to automate the allocation, rather than doing it by hand. It's still the same process, but you have a different allocation routine.

Because you select the payment record, and then record the allocation of that payment record against transactions, you can set up the form links to either record the payment record ID automatically, but sometimes it's manual. Not a manual number in the sense that the user has to type it in. Sometimes the form design allows the number to be allocated without code, because you are able to design the form so that Access knows that the payment record id has to go in the corresponding payment record id field on the transaction that was matched. Sometimes you need to set this reference number with code.

I hope this helps.

 

[HeadAgainstABrickWall2021]

If I may comment.

You don't want multiple payment subforms, just one.

You have a payments procedure to allocate payments.

So you have a payment form.
That form lets you record the payment ID, date, payment amount, settlement discount amount, payment source, reference numbers, and importantly, customer account reference, for each payment, one record at a time, together with anything else you need. The clerk doing the allocation, the date/time the allocation was processed, and so on.

This form could be a single form, or a continuous form. Whichever you find easier to manage and work with. A lot of places would have a manual list of payments to be processed to control and manage this payment allocation. Tick them off as you do them, so you know they are all done. Maintain a manual AR (Sales Ledger) control record that records the total outstanding balances that the AR ledger should be showing, so yuo know the processes are being operated correctly. That's what I did.

When you select the customer account, your allocation subform then shows all the unallocated transactions for the selected account. You can then mark the payment allocation against the outstanding items, and when the payments you enter agree with the total of the payment plus the discount, you can update those payments. The paymentid gets recorded against the payment allocations. You could have processes within this subform that let you match all transactions within a date range. Whatever it takes to make the process easy for the users.

It's not about the form design. It's about the data management really.

You need these tables.

customer table
customer transactions table (1 customer = m customer transactions - invoices, credits, journal; adjustments, payments etc)
payments table (effectively tying in with your banking records - this represents the payment form mentioned above)
payment allocation table (1 payment table entry = m customer transactions)

And then your processes manipulate the data in these tables. Get the tables right, and the process is much easier. Still complex practically, but not logically. Complex practically because a process that makes it easy for clerical staff to match a large payment with a large number of transactions, some of which will only be part payments is a complicated thing to get right. It's easy when customers pay one bill at a time. It's hard when they pay hundreds or even thousands of items at a time. Maybe they send in a spreadsheet with the payment, and you can can use that to automate the allocation, rather than doing it by hand. It's still the same process, but you have a different allocation routine.

I hope this helps.

Really appreciate you taking the time to give such a detailed response Dave. This is a bit beyond the scope of current project and current role but I have definitely taken it under advisement and will keep learning/training so that I understand it all!