Problem with my database

Hi folks, thanks for looking. I have a database that takes down order details.
Each customer has there own priceid in the price table, this is due to an opertaional requirment. A group or line discount field is no good.

I Since creating my Price table I cannot acces the add order details subform to choose my products, and there prices.
I set the AddOderDetailsSubform's Rowsource for the Products combo to be::p

SELECT DISTINCTROW Products.ProductID, Price.Price
FROM Products INNER JOIN Price ON Products.ProductID = Price.ProductID
WHERE (((Price.CustomerID)=[Forms]![Add an Order and details]![CustomerID]))
ORDER BY Products.ProductName;

In the After Update event of the control I put
:eek:
Me.Price = Me.ProductID.Column(2)
Im now getting an error where the Add order details subform is not working, I keep getting an sql error saying value after end.
I know nothing of sql/vb, I checked all the forms in the VB window but could not find any data after the end sub.
I am in a real pickle here guys and will be on here all night till I sort it.

I look forward to hearing from you.

Regards
Mark

re: Problem with my database

You have two columns you post isn't very clear but I do believe that you should be referencing column 1 as Me!roductID.Column(0) Column 2 as Me!ProductID.Column(1)

Your statement should read:
Me!Price = Me!ProductID.Column(1)

re: Problem with my database

Hi I changed that thank you for the help.
What I have found is that when I go into my add order details form I keep getting boxes that as for a parametor value, im dont know why.
I think the db is complex, can you take a look and tell me what I need to do.
Trying to find errors when you cant identify them is proving a nightmare

Thanks
Mark
.

re: Problem with my database

O.K. first look at the recordsource and verify you can open that with no issues. Then make sure the controls on that form have a control source that match up with the fields in the recordsource. hope this helps.

re: Problem with my database

HI thanks
I have checked the row source in the products combo box it currently reads
SELECT DISTISELECT DISTINCTROW tblproducts.ProductID, Price.Price
FROM Products INNER JOIN Price ON Products.ProductID = Price.ProductID
WHERE (((Price.CustomerID)=[Forms]![AddanOrderandDetails]![CustomerID]))
ORDER BY Products.tblProductName;.

can you see anything wrong with this.
The add new order form and the sub order form wont open without repeatdly asking me for certain values, like a search field.

I dont know if its my queries that are wrong, maybe that is why the forms dont work.
I have just made sure that all fields within my table have a value in them.

Dont really know where to go from here, I think the problems could be narrowed down to queries, forms, & onfiguration of events.

Thanks
Mark

re: Problem with my database

Right off I would suggest that you avoid naming a field the same as that of your table (Price.Price), that said.

Does it say "search" field on the prompt and are you sure it is this combo box? What exactly does the text on this prompt state. I am sure it probably has the name of a field it is looking for. Root around in the recordsource to start then hit all your combo boxes rowsources.

re: Problem with my database

Hi the errors that I get say enter parameter value for
products.unit price
Unit Price
Discount Price

Maybe a bit of history would help with this, the user said originally that they wanted to be able to give custoners a discount on every line of stock.
Then realised after completion that this is no good. The only option is to give each customer there own price list.

I have made big changes at the table level and the inherent errors are crawling can someone take a look at it and tell me what to do to sort it, pretty pls.

Thanks
Mark

re: Problem with my database

I have the structure
tblCustomers
CustomerID Autonumber and key
then other non relevent field at the mo

tblOrderDetails
OrderDetaiID Key
OrderID
ProductID
Quantity
Price

tblOrders
OrderID Key
CustomerID
OrderDate
PurchaseOrderNumber
ShippingMethodID
Payment Received
Comment

tblPrice
PriceID Key
ProductID
Price
CustomerID

tblproducts
productID Key
ProductName
Cost
If you r not getting the info you need can I send you copy of the db.
Thanks
Mark

re: Problem with my database

Open your order form in design view look at the properties section and view the recordsource. I am sure you will see several fields in there that contain what you are looking for. Such as "products.unit price" change to COST and "Discount Price" remove or replace with necesary field name....

re: Problem with my database

Hi it dsays
SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, OrderDetails.Discount FROM OrderDetails;

so im gonna edit it to

SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, OrderDetailss;

re: Problem with my database

Uh it looks like you only grabbed part of that SQL statement.

re: Problem with my database

erm
Sorry you lost me.

re: Problem with my database

The record source for the addorderdetails subform is
SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, OrderDetails.Discount FROM OrderDetails;
The record source for the order details subform is
Total Price of Order Details

Does this help.
thanks
mark

re: Problem with my database

No way that will work.

First I asked for the recordsource form the Orders form and you posted:

It Should read:



Then for the subform you pasted the same...
On your main form you would use the orders table the subform would have the details table. If you remove the bolded statement then you should look on the form for a control that has a controlsource to Discount and remove that also. I think we are making some headway.

re: Problem with my database

Is orderdetailID mispelled only in here or in your table also?

re: Problem with my database

so im gonna edit it to
SELECT OrderDetails.OrderDetailID, OrderDetails.OrderID, OrderDetails.ProductID, OrderDetails.Quantity, OrderDetailss;[/quote]

No way that will work.

First I asked for the recordsource form the Orders form and you posted:
superb, Thank You. let me just check through somthing with you
the forms I have are
AddanOderandDetail
AddorDeleteCustomer
AddorDeletePaymentMethods
AddorDeleteProducts
AddOrderDetailsSubform
ChangeOurCompanyInformation
CustomerOrdersSubfrorm
Reports
ViewReports

I just thought it might be useful
Thanks
Mark



It Should read:



Then for the subform you pasted the same...
On your main form you would use the orders table the subform would have the details table. If you remove the bolded statement then you should look on the form for a control that has a controlsource to Discount and remove that also. I think we are making some headway.[/quote]

re: Problem with my database

Looks like your rolling, any more questions feel free to post.

re: Problem with my database

You a star what can I say superb. Thanks

I made sure that the orderentry form's record was set as advised.
I did the same for the OrderDetailOrderForm,
Thinking back with this in mind I may have set up the products raw source to this value rather than what it should be, rather than where it was ment for, the record entry box.

I also ge a Syntax error (missin operator in query expression tblPrice INNER JOIN tblProducts.ProductID = tblPrice.ProducrID.

I persume this means missing () <>{}

I have also gone through the Macro's and objects checking that spelling and removal of spaces.

Thanks
Mark

re: Problem with my database

Hi is anyone out there lol, thanks for all your help goin bed

re: Problem with my database

The button options... (dmn I am tired) debug what is the yellow line?

We need the full sql statement (that is the error) trying to run at that moment (not ALL the code couple of lines maybe).
Good night.

re: Problem with my database

Morning campers

re: Problem with my database

Hi im not sure what it is you need.

Thanks
Mark

re: Problem with my database

I think this is related to the ordersubform
Private Sub cmdAddNewRecord_Click()
On Error GoTo Err_cmdAddNewRecord_Click

DoCmd.GoToRecord , , acNewRec
Forms![Add an Order and Details]![Order Details Subform].Form.Requery
Forms![Add an Order and Details]![OrderSubTotal].Requery
Forms![Add an Order and Details]![OrderTotal].Requery

Exit_cmdAddNewRecord_Click:
Exit Sub

Err_cmdAddNewRecord_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewRecord_Click

End Sub
and this is the addneorder form.


Err_cmdCloseOrderForm_Click:
MsgBox Err.Description
Resume Exit_cmdCloseOrderForm_Click

End Sub


Private Sub Delete_Current_Order_Click()
On Error GoTo Err_Delete_Current_Order_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.GoToRecord acDataForm, "Add Order and Details"


Exit_Delete_Current_Order_Click:
Exit Sub

Err_Delete_Current_Order_Click:
MsgBox Err.Description
Resume Exit_Delete_Current_Order_Click
End Sub

Private Sub Print_Click()
On Error GoTo Err_Print_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Exit_Print_Click:
Exit Sub

Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub

Private Sub cmdInvoice_Click()
On Error GoTo Err_cmdInvoice_Click
Dim stDocName As String
Dim stlinkcriteria As String

stDocName = "Report1" '/ Replace my report name with "Invoice"..
stlinkcriteria = "[CustomerID]=" & Me.CustomerID & "And [Orderid]=" &
Me.OrderID
DoCmd.OpenReport stDocName, acPreview, , stlinkcriteria

Exit_cmdInvoice_Click:
Exit Sub

Err_cmdInvoice_Click:
MsgBox Err.Description
Resume Exit_cmdInvoice_Click

End Sub
Private Sub Command121_Click()
On Error GoTo Err_Command121_Click

Dim stDocName As String
Dim MyForm As Form

stDocName = "Add an Order and Details"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True
DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False

Exit_Command121_Click:
Exit Sub

Err_Command121_Click:
MsgBox Err.Description
Resume Exit_Command121_Click

End Sub
Private Sub Command122_Click()
On Error GoTo Err_Command122_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection

Exit_Command122_Click:
Exit Sub

Err_Command122_Click:
MsgBox Err.Description
Resume Exit_Command122_Click

End Sub
Private Sub Command123_Click()
On Error GoTo Err_Command123_Click

Dim stDocName As String

stDocName = "Invoice"
DoCmd.OpenReport stDocName, acPreview

Exit_Command123_Click:
Exit Sub

Err_Command123_Click:
MsgBox Err.Description
Resume Exit_Command123_Click

End Sub

Is this what I need to edit. I havent a clue where

Regards
Mark

re: Problem with my database

I have removed all the spaces between Table names.


Regards
Mark

re: Problem with my database

Im still not working erm dont know what to try,
How much would someone charge me to have a look at th database and resolve its issues.


Thanks
Mark

re: Problem with my database

tblProducts.ProductID = tblPrice.ProducrID
Are both fields Numerical such as the tblProducts.ProductID is probably an autonumber and make sure tblPrice.ProducrID is set to number. Then we should check your relation ships.


Search for the following line in the code you sent:
Change it to this:
Might be the problem.