subform................?????

[halil]

subform........(help with college project)

hi there
im havin a problem wot i want to do is on the subform which can be downloaded below, when a product is selected i want it to change the price , to what it is in the products table

how do i do this

 

[Pat Hartman]

Add the price to the rowsource of the combo. Then in the combo's AfterUpdate Event:

Me.txtPrice = Me.txtProduct.Column(2)

This assumes that the price is the third column of the rowsouce. Change the field names as necessary.

 

[halil]

hi there
could u break down the formula
Me.txtPrice = Me.txtProduct.Column(2)

as i am a little confused about changing the fields

 

[WayneRyan]

halil,

What Pat means is change the rowsource of your
combo to something like:

Select ItemName, ThePrice
From YourTable
Order by ItemName;

This should all be on one line.

In the properties box put that there are two
columns, the width of the 2nd one can be 0"
which makes it invisible.

In the AfterUpdate Event for your combo put:

Me.txtPrice = Me.cboYourCombo.Column(2)

The Me.txtPrice is your control on the
right-hand side of your form. It will be assigned
the 2nd value in your select statement (ThePrice).

The cboYourCombo is the name of your combo.

You'll also have to change the field and
table names in the rowsource SQL.

hth,
Wayne

 

[halil]

thx wayne
i'll see if it works

 

[halil]

i have tried wot u sed but it says that it cant find the macro "ME"

 

[Mile-O]

Are you using the code builder to make the event procedure or have you put the cde in the AfterUdate() line?

It sounds like you've put it on the line - if you have click on the button at theright of the event and select Code Builder, and put the code there.

 

[halil]

thx but it diodnt work

i mite of not written the question properly
the image belowmay tell u wot i excatly want to do

i have also checked the northwind sample db and the order subform is exactly wot i want to do but it aint workin

 

[Pat Hartman]

I think that northwind.mdb uses DLookup() to do the same thing. Look at the order form in that db for an example that you may understand.

 

[halil]

hi there
pat ive had a look at the nrthwind db and found this

Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductID_AfterUpdate:
Exit Sub

Err_ProductID_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductID_AfterUpdate

End Sub


to me is gibberish so is it possible for you to look at my db and see what is going wrong
reply back please

thx

Halil

 

[halil]

anyone

 

[Mile-O]

Private Sub ProductID_AfterUpdate() 

' if an error occurs during the execution of this
' subroutine then send perform the procedure that
' the next line points to
On Error GoTo Err_ProductID_AfterUpdate 

' create a variable to store the filter we are going to use
' to remind us that it is a string variable we put [b]str[/b]
' at the front of it and call it Filter, hence strFilter
Dim strFilter As String 

' Evaluate filter before it's passed to DLookup function. 
' Here we are deciding what our criteria will be
' We know that we want to use the field called ProductID
' and we also know we are looking for the ProductID of the
' record in the table where that is equal to the object
' called ProductID 
strFilter = "ProductID = " & Me!ProductID 

' Look up product's unit price and assign it to UnitPrice control. 
' Here we are assigning the field called UnitPrice with a value
' that we are getting from the table. The DLookup is built with
' the following arguments. DLookup(FIELDNAME, TABLENAME, [CRITERIA]) - I've bracketed criteria as it is not always necessary.
' So we are looking for the price in the table called Products where, by using the filter, the ProductID field is equal to the chosen product
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter) 

' will exit the subroutine and is ended with an Exit Sub statement
' so that the code does carry on through and run the error handling
' section underneath
Exit_ProductID_AfterUpdate: 
Exit Sub 

' the area of code that deals with errors - this will just give a message
' box detailing the nature of the error
Err_ProductID_AfterUpdate: 
MsgBox Err.Description 
Resume Exit_ProductID_AfterUpdate 

End Sub

 

[halil]

hi i jus tried the code given and it still doesnt work

 

[Mile-O]

Have you changed the objects and fields in the code to those on your own database?

 

[halil]

yeh i did and it still aint workin

 

[halil]

could someone take a look at my db that i have made that has experince, i have asked my teachers and they dont really know how to fix the problem


thx in advance

 

[Pat Hartman]

Why don't you post your code and we will look at it.

 

[halil]

heres the code
for the frmorder form
Private Sub Order_Date_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub tblProduct_subform_Enter()

End Sub

Private Sub tblProduct_subform_Label_Click()

End Sub
Private Sub add_order_Click()
On Error GoTo Err_add_order_Click


DoCmd.GoToRecord , , acNewRec

Exit_add_order_Click:
Exit Sub

Err_add_order_Click:
MsgBox Err.Description
Resume Exit_add_order_Click

End Sub
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub
Private Sub Print_invoice_Click()
On Error GoTo Err_Print_invoice_Click


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

Exit_Print_invoice_Click:
Exit Sub

Err_Print_invoice_Click:
MsgBox Err.Description
Resume Exit_Print_invoice_Click

End Sub
Private Sub Close_order_form_Click()
On Error GoTo Err_Close_order_form_Click


DoCmd.Close

Exit_Close_order_form_Click:
Exit Sub

Err_Close_order_form_Click:
MsgBox Err.Description
Resume Exit_Close_order_form_Click

End Sub

 

[Mile-O]

Try replacng the DoCmd.DoMenuItem stuff with DoCmd.RunCommand to do the appropriate task.

 

[Mile-O]

Halil, after looking at your database all you need to do is put this line in the After_Update() event of your combobox called Price on your subform.

Me.Price = DLookup("[Price]", "tblProduct", "[Product Name] = '" & Me.[Product name] & "'")