Form Not Working Right

[emory]

I am new to Access having spent ages using excel

This one has got me completely stumped

[HansV]

Welcome to Eileen's Lounge!

See the attached version.

I have removed some superfluous fields, created a query qryCustomers and used that as Record Source for the form.

Feel free to ask more questions!

Blank Database.zip

[emory]

Hi Hans Many thanks for your help :fanfare

I have tried to follow what you have done and repeat the process

I notice that you have changed the data control for the form to data from the customers query. I had been adding data from the customers file using the “Add Existing Field” tab. Was that wrong?

I have created a table with newspaper adverts costs date etc.

In the customer file I have added a field called AdvertsID this is linked to Adverts table

Adverts
ID Publication DataID
1 Telegraph Weekend 1
2 The Oldie 2

This is then linked to the Data table

Data
ID Publication Publishing Date Issue Date Cost Size Notes
1 Telegraph Weekend 1.00 50mm x 52mm


The query works fine but I can’t get the data into my Form without changing the record source

Many thanks

Emory

[HansV]

To display fields from the Town and Counties tables on the Telesales form, you need to set the Record Source of the form to a query that includes those tables as well as the Customer table. You can't use just the Customer table as Record Source.
If you add an AdvertsID field to the Customer table, each customer can have only one related record in the Adverts table. Is that what you intended, or can a customer have several adverts?

[emory]

Hi Thanks

At this stage the customer is allocated to one advert then you can see if they have further orders it can be allocated to that campaign

I have a query as follows:

ID Title 1st Name Surname Publication Publishing Date Cost Size

This works fine do I need to add this data to the qrycustomers ?

Many thanks

[HansV]

Yes - use the Show Table dialog to add the Adverts table to qryCustomers.
Join it to the Customers table on the ID vs AdvertsID field.
Double-click the join line and select the option to "include ALL records from Customers and only those records from Adverts where the joined fields are equal", then click OK.
Add the AdvertsID field from Customers to the query grid, and the fields from Adverts that you want to display on the form.
Save and close the query.
If you now open the form in design view, the newly added fields will be available.

[emory]

Hi Hans


I have tried to follow what you said but I cant see what I am doing wrong I think when the penny drops it will be OK

Hope u can help

Many thanks

Database Advert.zip

[HansV]

You have linked Customers and Adverts on ID vs ID. You should link them on AdvertsID vs ID.
And the AdvertsID combo box on the form should have Adverts as Row Source, not Data.
See the attached version.

Database Advert.zip

I don't understand the role of Data - the publication name occurs in both Adverts and in Data...

[emory]

I didn't understand the role of Data either so its gone

It all works thanks

[emory]

Hi I have used the standard template down loaded from Microsoft "Customers Order database" I have transferred my data and my forms and queries no problem all working. I have set up the order form. Name address etc.

I cannot see how the unit price works its not picking it up in when I enter an order although my products are. I feel sure that is should be coming from the orders table but I cant see how its picked up


Order Detail ID Order ID Product ID Quantity Discount Unit Price
18 19 Balti Chicken 1 0.00%
19 19 Apple Pie 1 0.00%
20 Chicken & Mushroom 25 0.00%

Any help would be much appreciated

[HansV]

I only see an Access 2003 Customer orders database on the Mixrosoft site - is that the one you downloaded?

There should have been an After Update event procedure for the ProductID combo box on Order Details Subform:

Code: Select all

Private Sub ProductID_AfterUpdate()
    Me.UnitPrice = Me.ProductID.Column(2)
End Sub

This enters the UnitPrice of the selected product from the Products table into the UnitPrice text box on the form. The user can still edit the price.

[emory]

Hi

There is no plus sign to add one to the orders Table

I have a order details subform

Product Quantity Unit Price Discount
Steak & Kidney 1 #Name? 0.00%
Steak & Guinness 1 #Name? 0.00%

What is the connection
Many thanks

[HansV]

I don't know what you mean by "There is no plus sign to add one to the orders Table"...

What is the Control Source of the Unit Price text box on the subform?

[emory]

Hi Some how some where by fiddling its working

[HansV]

Uh... OK.

[Rudi]

Hi Some how some where by fiddling its working

Say what?

[Pat]

what