Solved question regarding form for data entry (1 Viewer)

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
In your Payment combo change the row source to
SELECT [T-PaymentTypes].PaymentTypeID_PK, [T-PaymentTypes].PaymentType FROM [T-PaymentTypes] ORDER BY [T-PaymentTypes].Order;

Also change the Control source to PaymentTypeID_FK.
To do that you'll need to bring it in to the forms underlying recordsource;
Code:
SELECT T_Payments.Musician_FK, T_Payments.EngagementsText_FK, T_Payments.DtPayment, T_Payments.Amount, T_Payments.VATReg, T_Payments.Notes, T_Payments.PaymentID, T_Payments.PaymentTypeID_FK FROM T_Payments;

The rest is as per the other combo's
Many thanks Minty, that works perfectly now!

In order to get the form to my proper database, can I just import it somehow? I'll have a look, maybe it's self explanatory. Otherwise, by describing how to do it with words was actually very useful! Thanks again.

Edit: I figured out how to import it now. Many thanks.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:51
Joined
May 7, 2009
Messages
19,169
see this changes to your form.
 

Attachments

  • sample (2).zip
    120.9 KB · Views: 88

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
Many thanks arnelgp.

On another tiny and insignificant point, I notice that when I scroll through the list of payments in the form on my normal database, they are in an unusual order. You wouldn't notice this on the sample version though. Is there an easy way to make sure that the form is set to ascending engagement order?

And within the engagement order, the engagements in order of Surname? Maybe I'm pushing my luck!

If it's complicated not to worry.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:51
Joined
May 7, 2009
Messages
19,169
i think the form is not using any sort order.
it is sorted on the order you Enter those data.
so if you enter one yesterday, it will come first, regardless
of engagement number.

you can force the sorting on the Form's Order By property
and Setting Order By On load to Yes.
 

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
i think the form is not using any sort order.
it is sorted on the order you Enter those data.
so if you enter one yesterday, it will come first, regardless
of engagement number.

you can force the sorting on the Form's Order By property
and Setting Order By On load to Yes.
Brilliant, thanks!

I found Order By On Load, and it's already set to yes. The box above is Order By. Would that one decide the order?
 
Last edited:

Minty

AWF VIP
Local time
Today, 21:51
Joined
Jul 26, 2013
Messages
10,355
It's in the FORM properties. Click the little square box in the top left hand corner of the form in design view ;
1622806872640.png
 

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
It's in the FORM properties. Click the little square box in the top left hand corner of the form in design view ;
View attachment 92124
Thanks Minty. I found it eventually, but it's already set to Yes. If I want the form in order of engagement, and within each engagement arranged in order of surname, would I need to enter something in the Order By box above?
 

Minty

AWF VIP
Local time
Today, 21:51
Joined
Jul 26, 2013
Messages
10,355
Yes - I don't normally use that, I normally set the order in the form's underlying query - it's just a personal preference.
 

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
Yes - I don't normally use that, I normally set the order in the form's underlying query - it's just a personal preference.
The thing is, it's in a different order to the table it relates to. If I were to use the Order By box for EngagementText, what's the format? I tried a few things but it's not worked yet.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:51
Joined
May 7, 2009
Messages
19,169
if it is not working, try setting the Order on your Recordsource.
 

Minty

AWF VIP
Local time
Today, 21:51
Joined
Jul 26, 2013
Messages
10,355
A couple of points you should be aware of - intrinsically data in a table has NO defined order. A table is merely a container for the data.
As a result the order you see raw table data in can change. If you don't define an order it will be determined by some underlying factor such as indexes etc.

So the only way to guarantee a display order is to use a query and set a specific order in that.
Hence make your forms row source a query based on the table and bring in all the fields you need to see, and any you want to use to set an underlying order.
 

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
A couple of points you should be aware of - intrinsically data in a table has NO defined order. A table is merely a container for the data.
As a result the order you see raw table data in can change. If you don't define an order it will be determined by some underlying factor such as indexes etc.

So the only way to guarantee a display order is to use a query and set a specific order in that.
Hence make your forms row source a query based on the table and bring in all the fields you need to see, and any you want to use to set an underlying order.
I see, thanks for that. I'll give that a go.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:51
Joined
May 7, 2009
Messages
19,169
add the musician table to your recordsource if you want to sort by surname:
 

Attachments

  • sample (2).zip
    121.4 KB · Views: 122

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
I have one final question I hope. I wrote some VBA code to populate some of the new form fields with the previous field data, and that's all working great.

I inserted a New Record button that initiates the VBA code. However, the last entry I just entered doesn't seem to automatically update the T_Payments table, until I open or refresh that table. Therefore, the new fields on the form still shows the previous latest data, if that makes sense.

My temporary solution was to insert a Save button, so I just save each new form entry first, then click on the New Record button.

Is there a more efficient way of solving this please? I wondered whether there was some kind of command to put at the beginning of the VBA code, to refresh or save the T_Payments table.

Thanks again for your help.
 

neilsolaris

Member
Local time
Today, 21:51
Joined
Apr 19, 2020
Messages
114
wouldn't it be nice to see how much have been paid?
I was just having another look at your totals subform now, and I'd like to incorporate that idea. Would it be easy to show just those payments belonging to whichever engagement is selected on the main form?
 

Users who are viewing this thread

Top Bottom