Form based on a table but with a drop down select box 1

[kattzndagz]

Hi everyone

I am missing something simple here i am sure
I have a table

ServiceCompany
PK ServiceCoId Autonumber
FK EventID Number
CompanyName
CompanyAddress
ServiceProvided
ServiceCost

I have a form (mainfrm) which is based on tblevent where i have details on the type of event we are holding

I have a tabcontrol with EventAttendedsubform details on one tab and i want the ServiceCostssubform on the second tab

On the ServiceCostsubform, I want the user to be able to select from a list of Company names from the Service Company Table.

How do i create a drop down box that will show all the Companies from them to select from, and then populate other detail such as the address etc. when one is selected?

Once they have selected the company, they would then enter the service provided and the service cost

I have tried having the Service provided and the service cost in a separate table but that isn't really necessary due to the company generally on providing one type of service eg marquee hire, food etc

Or, would it be better to have them as two separate tables?

Also, if a company is not on the list, i need for them to be able to go to the ServiceCompanyFrm to complete new details then go back to the mainfrm to continue.

What am i missing?
thanks for your help

 

[dhookom]

Are we to assume there is a table as the record source of the ServiceCostssubform that contains a foreign key to the ServiceCompany table? What do you mean by "populate"? I trust you aren't storing the address etc multiple times in your database.

Duane
Hook'D on Access
MS Access MVP

 

[kattzndagz]

Hi Duane

Thanks for the reply

No im not storing the addresses in multiple places Just in the appropriate tables

I have the following tables:

TblClient
PK ClientID
CompanyName
Address
Phone
etc

TblClientContact
PK ContactID
FK ClientID
Title
FirstName
LastName
Position
Phone
Email

TblEvent
PK EventID
FK Event Type (via a lookup table)
EventDate
EventTime
Location
Budget
NoOfPlaces

TblEventAttended
PK EventAttendedID
FK ContactID
RSVP (lookup- accept or decline)
NonAttendance yes/no


TblServiceCo
PK ServiceCoID
FK EventID
CompanyName
CompanyAddress
ServiceProvided
Cost


I have a form "mainform" based on TblEvent and the code is:

SELECT TblEvent.EventID, TblEvent.EventType, TblEvent.ClientID, TblEvent.EventTheme, TblEvent.EventDate, TblEvent.Location, TblEvent.StartTime, TblEvent.Budget, TblEvent.NumberOfPlaces, TblEvent.Notes
FROM TblEvent;

On the "mainform" i have a subform "eventattendsubform" which is located on a tabcontrol. This subform is based on TbleEventAttended and the code for that is:

SELECT TblEventAttended.EventAttendedID, TblEventAttended.EventID, TblEventAttended.ContactID, TblEventAttended.RSVP, TblEventAttended.NonAttendance, TblClient.CompanyName
FROM (TblClient INNER JOIN TblClientContact ON TblClient.ClientID = TblClientContact.ClientID) INNER JOIN TblEventAttended ON TblClientContact.ContactID = TblEventAttended.ContactID;

On the second tab i would like the user to be able to enter the costs related to the event. She would be able to select from a drop down box any of the companies that are listed in the TbleServiceCo. When she selects the company, i would like the other controls to be automatically filled in with the address details, (just for the users benefit as it would be easier for them) then she would enter the type of service provided and the costs associated with that service.

Also, if the service company is not listed in the drop down box, she needs to be able to go to the table (via a form which i will create) to enter the necessary data - then to come back to the "mainfrm" where she left off.

Are my tables set up correctly to do this? and if so, how do I do this? or what am I missing here? I don't actually work with SQL, i do it all the old fashioned way but i am learning.

I hope i have explained this correctly, i wish could attach pictures etc, but as i am at work, i don't have access to anywhere online i can store anything.

I would just like to say a BIG thankyou to you, as you have helped me through this forum, so much over the past years. I have learnt so much and my databases are getting better and better.

 

[dhookom]

Do you only ever use a service company for one event? I think I would pull the event ID out of the last table and create a junction table of
[tt][blue]
tblServiceEvent
==============
ServEventID autonumber primary key
EventID FK
ServiceCoID FK
ServiceID FK to a table of types of services
Qty
Cost
[/blue][/tt]

Duane
Hook'D on Access
MS Access MVP