Solved question regarding form for data entry

[neilsolaris]

Hi,

I have what I hope is a simple question, if I can work out how to phrase it! Basically, I have a table, and two of the fields are foreign keys. What I'd like to do is to create a form, so that I can easily add data to this table. However, in the form I would like to see a drop down list for these fields, but I'd like to view the primary key they relate to in the drop down list.

What would be the easiest way to achieve that?

If need be I can create a sample database, if that makes it easier.

Many thanks for your help.

 

[Minty]

Sound like you need 2 combo boxes?

Set the row source for each one to the table the FK's relate to.

 

[neilsolaris]

Sound like you need 2 combo boxes?

Set the row source for each one to the table the FK's relate to.

Great, thanks a lot Minty. I'll give that a go and report back.

 

[Minty]

If you get stuck post up a copy of what you have, but I think if you use the wizard for the first one it will show you how to set it up manually.

 

[neilsolaris]

I've attached a sample version, with my attempt so far. It's work in progress though.

With the F_payments, my aim is to link it to the Full_Name field in the query Q_MusiciansDetails.

And with EngagementsText to link it to EngagementsText_PK in the T_Engagements table.

Am I on the right lines so far?

Many thanks.

 

[Minty]

Have a look at how I have formatted the Musician combo, and the underlying query.
The secret is to hide the bound column, (The FK_ID) by setting the column width to 0.

You should be able to apply the same technique to the Engagement combo.
Report back if you get stuck.

 

[neilsolaris]

Have a look at how I have formatted the Musician combo, and the underlying query.
The secret is to hide the bound column, (The FK_ID) by setting the column width to 0.

You should be able to apply the same technique to the Engagement combo.
Report back if you get stuck.

Thanks a lot. I'm not able to open it though. I should have said, I'm using Access version 2007. Are you able to save it as 2007 please?

 

[Minty]

That's weird - It opened straight away (I'm on O365) and in the header it says 2007-2016 format.
Try this one.

 

[neilsolaris]

Thanks. Unfortunately it's still saying Unrecognized Format. I don't know why. Maybe I can try to apply your changes by description?

 

[Minty]

Does this work?

 

[neilsolaris]

Unfortunately not!

 

[Minty]

Lol - okay - lets try doing it by words.

1. Change the row source of the Musician combo to

SELECT Q_MusicianDetails.Musician_PK, Q_MusicianDetails.Full_Name FROM Q_MusicianDetails ORDER BY Q_MusicianDetails.Full_Name;

2. Change the number of columns to 2
3. Change the column widths to 0 ; 5 as per picture below

Bob should be your uncle.

 

[neilsolaris]

That works perfectly, thanks a lot!

In my haste, I forgot to add the payment type combobox. But I'll see if I can make it work by copying what you did above. I'll let you know if I get stuck.

 

[Minty]

Note that you can also make the drop down list width bigger than the control - handy for displaying long text for the user to choose when space is limited.

 

[arnelgp]

wouldn't it be nice to see how much have been paid?

 

[neilsolaris]

Thanks for the tip, that sounds really helpful.

I've attached my database again, because I'm a struggling to create the payment type combobox. Basically, in the form I'd like the Payment Type to show the PaymentType field from the T_PaymentTypes table. Could you possibly talk me through that one please? I've attached the database again.

 

[neilsolaris]

wouldn't it be nice to see how much have been paid?

Many thanks for preparing that. This is just a sample, in my full database I have a query showing the totals for each musician/engagement. This form will be just for data entry.

If I have a large amount of data I will import it from a spreadsheet, but for small amounts of data I think this will be easier.

 

[arnelgp]

it is a Glance look (dashboard?).
so you got an idea of something.
making data entry more interesting!

 

[neilsolaris]

it is a Glance look (dashboard?).
so you got an idea of something.
making data entry more interesting!

I see, that makes sense! I think when I finished solving this bit then I'll move on to incorporate your idea. My brain gets a bit confused otherwise!

What I also aim to do, is to write VBA code, so that when I move to a new form entry, it copes the musician, engagement text and date of payment to the new entry. That should save a bit of time when entering the data.

 

[Minty]

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;

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