If WireBound = True Then Val(WirePrice) = ??

[Salochin]

Hi Guys.. still trying to do this on my own but need help with below code.. im slowly getting there and loving it

(so wish I had discovered this stuff years ago, im actually enjoying my self while at work... :-) )

im not sure if ive got it queit right.. Basics:

I have a yes/no tick box [WireBound] once clicked
allows [WirePrice] box to calculate and to show:
[NumberOfBox] ( which user types in Number in "123" format)
times by (ExtraPricePerUnit) which is in another form, tbl or query


Private Sub WireBound_Click()

If WireBound = True Then Val(WirePrice) = Val(NumberOfWireBound) * Forms![frmExtras]!Val(ExtraPricePerUnit)

End Sub

now it dosnt show anything in the box [WirePrice]
Should i place code in [WirePrice] and code [WireBound] something like
If True then goto [WirePrice] or place it all in a modual as I have 8 more chek boxes for this to work with..

As Always thanks in anticipation

Sal

[pod]

I am not sure what is the problem without recreating your project. But my suggestions is verify what you get from those fields onChange or onClick then you can spot the culprit and work on a solution.

Private Sub WireBound_Click()
    msgbox ("WireBound = " & WireBound)
    msgbox ("WirePrice  = " & Val(WirePrice) )
    msgbox ("ExtraPricePerUnit= " & Forms![frmExtras]!Val(ExtraPricePerUnit)
End Sub
 

[Salochin]

A freind suggested this as my answer to the problem:

Private Sub RingBinder_Click()
Dim unitPrice As Double
Dim db As Database
Dim rs As Recordset
Dim sql As String

If Me.RingBinder.Value = True Then
Set db = CurrentDb
sql = "Select ExtraPricePerUnit from tblExtras where ExtrasDescription = ""Ring Binders"""
Set rs = db.OpenRecordset(sql)
unitPrice = rs.Fields(0).Value
Me!RingBinder = Me!NumberOfRingBindersBound.Value * unitPrice
Else
Me!RingBinder = 0
End If

End Sub


It appears to work fine (at moment) he says its Dynamic ???

[pod]

The logic looks good

[Salochin]

hmm except on using it in the other check boxes it messes up.. I have changed it in the right areas to go on other check boxes to manipulate other data but once clicked the original box increases in value ?? *bangs head on desk*

Ive attached in zip my first ever project trying to use vb access.. so please except it will be a tad erhm wrong in places..
any chance you can see where im going wrong ?? huh first stumbling block and im stuck how embaresing...

[Salochin]

ah just found it user error I didnt replace all I should have (school boy error))

[pod]

Good for you
I was about to post it :) ... always feels good to find it ourselves :)

[Salochin]

Thanks and your right.. I keep trying never like the easy option as im trying to learn even if you do get some hints and tips doing it your self is much more rewarding at the end ..

now it aint working on others even when ive got the bits put in arghh *is still smiling* I will get there ...

any feed back on my access is always welcome if anything is glaringly bad, by the way..

[pod]

one thing to remember when programming is : use a function when repetitive code is present. I see that you want to do basically the same function with the seven choices of parts.

try using a function with parameters. see below

Also you might want to call this sub from a change in the number of units field as well.... with condition of course

Private Sub RingBinder_Click()
    Call setprice("Wire Binders", Me.WireBound, Me.NumberOfWireBound, Me.WirePrice)
End Sub
Private Sub WireBound_Click()
    Call setprice("Ring Binders", Me.RingBinder, Me.NumberOfRingBinder, Me.RingBinderPrice)
End Sub
Private Sub DVD_Click()
    Call setprice("DVD", Me.DVD, Me.NumberOfDVD, Me.DVDPrice)
End Sub
 
Sub setprice(objDesc As String, objCheck As Control, objNumberOf As Control, objPrice As Control)
 
    Dim unitPrice As Double
    Dim db As Database
    Dim rs As Recordset
    Dim sql As String
 
    If objCheck.Value = True Then
        Set db = CurrentDb
        sql = "Select ExtraPricePerUnit from tblExtras where ExtrasDescription = """ & objDesc & """"
        Set rs = db.OpenRecordset(sql)
        unitPrice = rs.Fields(0).Value
        objPrice = objNumberOf.Value * unitPrice
    Else
        objPrice = 0
    End If
 
 
End Sub

[Salochin]

Thanks I wil try that..

Question on line:

sql = "Select ExtraPricePerUnit from tblExtras where ExtrasDescription = """ & objDesc & """"

im guessing it knows to filter becouse of the code such as:

Private Sub WireBinder_Click()
Call setprice("Wire Binders", Me.WireBound, Me.NumberOfWireBound, Me.WirePrice)
End Sub

That allows the Sql """ & objDesc & """" find it ?

erhm if that made sense.. ?!

[Salochin]

ok what im finding with both your way and the way I was doing it is: your way is tidier then me just repeating whole script (thanks for that note) but only the first two check boxes work and do as they should.. Ive messed around swapping code and position of boxes etc too but no matter what it dosnt seem to replecate the out come ie .. no price is shown what so ever in any of the otehr boxes as they do in the first two.. ?!

[pod]

The "Sub setprice" is the main sub.
You still need to create "[Event Procedures]" for each checkboxes, (seven total including the three existing ones in the preceding code) the _Click events such as


Private Sub [fieldname]_Click()

and from each of those events, you must call the main sub with the right parameters, the ExtrasDescription (a string) and three control names; one checkbox and two textboxes

[pod]

You are right, there was a flaw in my eventprocedures I had the WireBound calling changes for the RingBinders and vice versa ... Confusing part names :)

See code below for corrections

Private Sub WireBound_Click()
Call setprice("Wire Binders", Me.WireBound, Me.NumberOfWireBound, Me.WirePrice)
End Sub
Private Sub RingBinder_Click()
Call setprice("Ring Binders", Me.RingBinder, Me.NumberOfRingBinder, Me.RingBinderPrice)
End Sub

[Salochin]

yes I changed them back over *at least I think* damn im enjoying this stuff thanks for your help...