added table now cannot enter data (1 Viewer)

steve111 · Aug 29, 2015

hi , I have a form that is created by a select query and all is working ok and I can add or change data in that form
I have now created another table and put it into the select query and it runs ok and gives me the results I need

but the problem is I cannot now add or change any data in that form
I am lost how to solve that problem

thanks steve

GinaWhipp · Aug 29, 2015

It's not the Form that is the problem it is the query. By adding that table it sounds like you made the query Read Only. You need to adjust your query to be Read/Write and your Form will be fine.

plog · Aug 29, 2015

I'm a proponent of 1 form-1 table. Forms that interact with data (add/edit/delete) should only be built on tables, not queries. Otherwise you get issues like this, or worse. When you base a form on a table, you simplify the equation and know exactly what any changes to the form act on (just one table).

I vote you redo your forms so that they are based on tables. If you have a one-to many relationship, you create a main table (created on the one side) and then a sub-form (based on the many side).

steve111 · Aug 30, 2015

hi
I have attached a sample of my form and tables
the form " order details subform" does let me edit and add, but when the product cost table is attached attached " see copy of order detail subform" I cannot add or edit . but I need to
unless there is another way to get the cost into the form
thanks for any help

steve

HiTechCoach · Aug 30, 2015

Stev,

I think this is related to your previous post :

http://www.access-programmers.co.uk/forums/showthread.php?t=280293

Adding he product cost table to the order sub form's record source will not work. That will cause thr recordset to become non-updateable. You will need to come up with a method to lookup the cost when needed.

I will take a look at your example and post back shortly.

steve111 · Aug 31, 2015

hi
yes it was related to it. the idea of the new table was the correct method but did come unstuck with this part

please see the form sales orders it has the subform in it where I need the costs

thanks steve

HiTechCoach · Aug 31, 2015

There are some design issues where your foreign key is not the Primary key field for the related table. You should always use MaterialID (primary key) from the Stocklist as the foreign key value stored in related tables not the StockNumber.

I am not sure what field to use as the date to look up the product cost. Is it Received Date form the Orders table?

HiTechCoach · Aug 31, 2015

I also see data type issues. Example: Price, Cost etc should be data type currency.

Getting the table design correct really is critical to the success of the application.

TIP: Avoid Spaces in any object name! It only makes thins more difficult.

HiTechCoach · Aug 31, 2015

I made a few of the changes that I felt were required to makes this work correctly.

I created a VBA code module that has two functions. This gives you two methods to look up the cost based on where you might need it. They can be used anywhere you need the cost like queries, forms, reports, etc.

See attached.

Here is the VBA code for the two functions:

Code:

Option Compare Database
Option Explicit


Public Function GetStockItemCostByDate(pMaterialID As Long, pCostDate As Date) As Currency

Dim currCost As Currency

currCost = 0

currCost = Nz(DLookup("[costs]", "[product cost]", "[MaterialID]=" & pMaterialID & " AND ([startdate] <=#" & pCostDate & "# AND [enddate] >= #" & pCostDate & "#)"), 0)


GetStockItemCostByDate = currCost

End Function

Public Function GetStockItemCostByOrderID(pMaterialID As Long, pOrderID As Long) As Currency

Dim currCost As Currency
Dim datOrderDate As Date


datOrderDate = Nz(DLookup("[Received]", "[Orders]", "[OrderID]=" & pOrderID), 0)

currCost = 0

currCost = Nz(DLookup("[costs]", "[product cost]", "[MaterialID]=" & pMaterialID & " AND ([startdate] <=#" & datOrderDate & "# AND [enddate] >= #" & datOrderDate & "#)"), 0)


GetStockItemCostByOrderID = currCost

End Function

Here is how is was used in the Order detail sub form's record soruce:

Code:

SELECT
[Order Details].OrderID, 
[Order Details].MaterialID, 
[Order Details].ItemDescription, 
[Order Details].shipped, 
[Order Details].ordered, 
[Order Details].OrderQty, 
[Order Details].[sell Price], 
[Order Details].Required, 
[Order Details].Currency, 
[Order Details].Notes, 
[Order Details].[Schedule date], 

GetStockItemCostByOrderID(Nz([MaterialID],0),Nz([OrderID],0)) AS ItemCost

FROM [Order Details];

The last field is a calculated column to get the cost.

steve111 · Aug 31, 2015

hi , I have managed to get this working so far
but I have got a problem
the costs do not come on the next quote I raise I have to wait until I raise the order, can we get it that the costs come when I raise the quote

thanks
steve

steve111 · Aug 31, 2015

hi

thanks for all your help on this it seems t wrking fine now , I did manage to change the it to the quote form

I have tried to replicate what you did without success on my purchasing form using date2 on that form, I got the costs in but they are still using the date from the quote form. could you please guide me where I went wrong

could you please explain to me about the materialid primary and foreign keys as I really thought the way I was doing it was correct and was changing forms etc to make it like mine was.

I though partno to stock number was the correct not to materialid as this shows numbers not parts. really confused

thanks for your time
steve

steve111 · Sep 1, 2015

hi ,

thanks for your help

I have managed to sort out how to make it work on another form now.
I have been asked if it is possible to have 3 costs per part
up to 5 parts cost "x" 5 parts to 20 parts cost " x1" and above 20 parts cost "x2 " then depending on the qty I want to buy or sell that price would appear as we do get discount for the quantity we buy

I have put the other part of my database on ie the purchasing form

thanks steve

HiTechCoach · Sep 1, 2015

steve111 said:
I have managed to sort out how to make it work on another form now.
I have been asked if it is possible to have 3 costs per part
up to 5 parts cost "x" 5 parts to 20 parts cost " x1" and above 20 parts cost "x2 " then depending on the qty I want to buy or sell that price would appear as we do get discount for the quantity we buy

Steve,

Glad to here you are making progress.

This works the same for selling price or costs. You will need additional fields for the quantity range like you did a date range. Same concept.

HiTechCoach · Sep 1, 2015

steve111 said:
hi

I though partno to stock number was the correct not to materialid as this shows numbers not parts. really confused

thanks for your time
steve

Steve,

You're welcome. Glad I can assist.

You are confusing what the users sees with how a good database works internally. The user works with a stock number while the application works with the materialid.

In most databases the primary key is a system assigned number. Also the end user rarely see it. The see something that is more meaningful to them.

Since materialid is the primary key field it MUST be used in all relationships to maintain Referential Integrity of the data. Important to do! The user will never need to see the materialid. To the end user he stock number is all they will see.

In a database application there is the proper way to you store data and the way you present data to the user. The end user rarely sees hows the data is actually stored. And they shouldn't. It is the developers responsibility to take the time to create a User Interface that takes the data and presents it in a meaningful way to the end user.

added table now cannot enter data (1 Viewer)

steve111

Registered User.

GinaWhipp

AWF VIP

plog

Banishment Pending

steve111

Registered User.

Attachments

HiTechCoach

Well-known member

steve111

Registered User.

Attachments

HiTechCoach

Well-known member

HiTechCoach

Well-known member

HiTechCoach

Well-known member

Attachments

steve111

Registered User.

steve111

Registered User.

Attachments

steve111

Registered User.

Attachments

HiTechCoach

Well-known member

HiTechCoach

Well-known member

Similar threads

Users who are viewing this thread