Drop Down Field Question

[tn_developer]

Hi,

I have two fairly simple tables as shown below.

PACKAGE_PROVIDER
PackageProviderID (AutoNumber)
PackageProvider (Text)

PACKAGE
PackageID (AutoNumber)
PackageName (Text)
PackageDate (Date/Time)
PackageProviderID (Number)
Cost (Currency)


NumberofSesions (Number)

So these "packages" are things like Groupon deals and the PACKAGE_PROVIDER table would store just that - Groupon, Living Social, Moolala, etc. This is for a friend who has a small business and each package provides a number of sessions of personal training.

I have created a One-to-Many link between these two tables on PackageProviderID and a form based on the PACKAGE table so that she can enter which packages she has offered. What I am trying to do now is show the Package Provider in a drop down in this form so she can just select it but have it populate the PackageProviderID field. I am not sure how to go about this. I have done some searches in Access Help and online, but I am not finding what I am looking for. Can someone point me in the right direction?

Thank you,
~k

[tn_developer]

Never mind....just figured it out. Sorry!

[tn_developer]

Well, I have this figured out on one form, but now can't get it to work on another form. I am not sure what I am doing wrong. This is a form with a subform. The main form has the client table. The subform has the "sessions" table which also has a PackageID in it. I have a one-to-many relationship set between the packages table and the sessions table. When I am in the form and go to the available fields for this form, the Packages table is not even showing.

Can someone point me in the right direction as to how to populate the PackageID in the Sessions table, but show the users the PackageName in a drop down field on the form?

Here are the tables.

CLIENT
ClientID (AutoNumber)
FirstName (Text)
LastName (Text)
....

SESSIONS
SessionID (Auto Number)
ClientID (Number)
SessionDate (Date/Time)
PackageID (Number)
Cost (Currency)

PACKAGE
PackageID (AutoNumber)
PackageName (Text)
PackageDate (Date/Time)
PackageProviderID (Number)
Cost (Currency)
NumberofSesions (Number)

Thank you,
~k

[orange]

You have a form/subform arrangement. Did you set up the master and Child links? The subform stuff should be automatic if set up as form/subform.
I don't have 2007, but here is a link that should help.
http://office.microsoft.com/en-us/ac...010098674.aspx

[tn_developer]

You have a form/subform arrangement. Did you set up the master and Child links? The subform stuff should be automatic if set up as form/subform.
I don't have 2007, but here is a link that should help.
http://office.microsoft.com/en-us/ac...010098674.aspx

Hi orange,

Thank you for the reply. The form/subform part works for all the fields in the session table like the date, cost, etc. It saves them correctly. What I am having trouble with is displaying the PackageName on that form since I have the PackageID in the Sessions table. On the other form, where I did get it to work, the ControlSource of that field is the ID, and in the source I have a select statement for the name, but I am not sure how I went about doing so. I am trying to do the same thing on this form but it's just not working for various reasons. No error message. I either get the ID to show up if I make the control source the PackageID or the package name if i make the control source the PackageName, but then the ID does not get populated.

Thank you,
~k

[orange]

What is the record source of the Main form? and of the subform?

In form design look at the properties of each form, look for the Data tab (I have access 2003), then the record source.
Paste these record sources.

[tn_developer]

What is the record source of the Main form? and of the subform?

In form design look at the properties of each form, look for the Data tab (I have access 2003), then the record source.
Paste these record sources.

Hi,

The record source for the main form is the Client table. For the subform it is the following query:

SELECT Package.[Package Name], Session.PackageID
FROM Package INNER JOIN [Session] ON Package.PackageID = Session.PackageID;

I assume that is wrong? I am not sure how I did this. It's obvious I am new to Access!!

[orange]

On your subform do you have a control for PackageId and PackageName?
Your main and subform have ClientId as a link?
Can you post a jpg of your form(s)?

[tn_developer]

On your subform do you have a control for PackageId and PackageName?
Your main and subform have ClientId as a link?
Can you post a jpg of your form(s)?

Hi,

I am not showing the ClientID on this form, but the main and subforms are linked by this ID. I am attaching JPGs of the ClientSessions form in form view and design view.



I actually don't want to show the PackageID in the subform either, just the PackageName but have it populate the PackageID.

I did this in another form and like I said, can't seem to duplicate it. Below is the form in which it works.

The difference is that there is no subform, but the PackageProviderID shown in this image actually displays the PackageProviderName, but populates the ID field.



Thank you,
~k

[orange]

In the client sessions subform you show a title of PackageName, but in the details you're showing PackageID?

[tn_developer]

In the client sessions subform you show a title of PackageName, but in the details you're showing PackageID?

Hi,

Yes, I am going in circles. However, if you look at the Packages form, it is set up the same way. The control source is the PackageProviderID, but what displays in the drop down is the actual PackageProvider (the name - not the ID). The row source is as follows:

SELECT PackageProvider.PackageProviderID, PackageProvider.PackageProvider
FROM PackageProvider
ORDER BY PackageProvider.[PackageProvider];



I was trying to set it up the same way in the ClientSession subform, but it is not working.

So the question is, how would you go about doing what I am trying to do?

Thank you,
~k

[orange]

Using the form shown in the last post, check the Format tab in the property sheet.

See what the measurements are. Post a jpg

[tn_developer]

Is this what you are looking for?

[orange]

Instead of the whole form can you select the combo15 like you did in the previous post?
Can you make an mdb version of your database and post it? I have 2003 and can not use an accdb format database.

[tn_developer]

Instead of the whole form can you select the combo15 like you did in the previous post?
Can you make an mdb version of your database and post it? I have 2003 and can not use an accdb format database.

Here is the JPG but I am unable to save the database in an earlier version. I get an error message indicating that the database has features that are not available in 2003. Then my only option is to click ok.