VB Coding

[abz_y2k]

Hi,

I just created a database for work, its got two fields on the form, item and Price.

What i want it to do is when I enter the amount of item, I want it to automatically work out (price = £3.99)the price per item and display it on the Price text box on access.

I done VB but cant remember much, i Know u might have to do a IF statement.

any ideas how this could be done.

 

[dan231]

try this code on your form properties:

Private Sub Form_Current()
txtGrandTotal = txtItemQty * txtItemPrice
End Sub

and also add the same code to your Grand Total text box.

Private Sub total_GotFocus()
txtGrandTotal = txtItemQty * txtItemPrice
End Sub

 

[abz_y2k]

hi mate, thanks for the response, still having difficulty making it work, can i send you the database so u can have a look?

 

[dan231]

you can zip it and attach it if it's not too big and you are not worried about people looking at your data.

 

[abz_y2k]

hi there mate, heres the database, theres no data.

so what i want it to do is when i enter number of item of the left text box, and taking into account the price of the item is £3.99, i want ti to do the calculation n dispay it on the box on the right


also how do you access the "On Load property of the form "



thanks

 

[dan231]

To get to the Form properties:
View your form in Form Design.
Then open the properties window (on any object)
In the Top dropdown of the properties window, select Form

~~~

What I did for your calculations. (I renamed your text boxes to make it easier to calculate, so take that in mind when you see the code)

In the properties for textbox: 12 months disposable carbon monoxide detector item (renamed to: CoQty)

Go to the Event tab
Select After Update
and Select Code Builder and add the code in Blue

Private Sub CoQty_AfterUpdate()
CoCost = CoQty * 3.99
End Sub

The CoCost represents your textbox: 12 months disposable carbon monoxide detector cost.

 

[abz_y2k]

Many many thanks dan. much Appreciated

 

[dan231]

glad to help out.

 

[abz_y2k]

still cant get to the onload property of the form.

 

[dan231]

Just add the code to the AfterUpdate of the Quanity textbox.

You don't have to add it to the form properties.

 

[abz_y2k]

hi, soz i got u bit coonfused i think,

the reason i need to go to the onload property fo the form is because when i load the form i want it to be locked, I got the code but can't seem to figure the fon load orm property out

 

[dan231]

To get to the actual Form properties:
View your form in Form Design.
Then open the properties window (on any object)
In the Top dropdown of the properties window, select Form
Then go to Event and choose OnLoad

 

[Mike375]

When you view the form in Design, scroll across so that you are clear of the actual form. Then right click, properties, event.

 

[abz_y2k]

Hi dan, one question.

You seen my database, if i was wanted to add the total up and display it on grand total box will i have to do something like this


Grand_total = CarbonCost + GrabrailCost + UndoCost?

if so what would u use as "Private Sub "?

 

[dan231]

Yes, you would have another text box that grand totals all of your total boxes. I'd have to play around to figure out exactly where this code would go, but you could try adding it to the form properties in the OnCurrent event.

 

[abz_y2k]

been fiddling around wiht the database whole morning , but no success

 

[dan231]

For your Form: OnCurrent event code:

Try somethign like this:

Private Sub Form_Current()
Me.Text14 = (Me.Text10 + Me.Text18)
End Sub

Where Me.Text14 is the grand total textbox and Me.Text10 and Me.Text18 are total textboxs in your form. This is where shorter, more concise textbox naming comes in handy, so you won't have large text to type. I would also use quotes " " for any textbox names that have spaces in them.

 

[rsmonkey]

just had a quick perouse on your db abz_y2k and their are quite a few fundamental design flaws if i didnt just see a stripped down db! From the looks of it your are trying to create a booking/tracking db where you can book jobs etc..

First and foremost a few of the design flaws:

1. You dont have a proper primary key that i can see anyway, as you are probably aware a PK has to be unique so that your can identify jobs. A PK almost certainly has to be assigned by access/pc as its to open for human error. Obviously autonumbers are the simplest way to do this. You have your PK set as address which would be fine if an address only ever needed 1 job, however if an address requires say one job now and then another job a couple months later your gunna hit some brick walls!

2. Table Structures: are all over the place.. for example on table1 you have 5 different fields for Ethnicity. Remember that a db is a smart filing system so if you were to look at youre table1 with data in it there are going to be alot of gaps etc... I suggest for elements such as these you create a seperate table and then place these option in their. For instance with ethnicity you would have a table named Ethnicity and in this table would be 2 columns: A Primary Key & Ethnictiy. This way on your booking table ethnicity can simply be referred to by its PK which will make your DB faster at referencing & indexing as well as saving lots of space if your database grows with jobs etc..

Think of it this way a DB is simple logic & organisation. Say you have a 2 lists of a million records both exactly the same but one of them had an extra column that numbered each record within it so 1-1000000. Then i told you to find a certain address on the first sheet and then on the second sheet i simply said find this number obviously your gonna find that number alot quicker.

Im leave it at that for now im not trying to get at your but i strongly suggest you have a good read up on database design & normalisation b4 you progress otherwise your just going to dig yourself a deeper & deeper hole.

Hope this helps

 

[abz_y2k]

tried that, messed around different ways, no success