SQL Queries inside vb code

[Chunk]

I want to run an sql query inside a function i have written in vb. I need the function to return the result of the query.

can anyone help?

Also, if I have written a function that returns an int, and i am then displaying this on a form, by setting the control source of a text field to the result of the function. How can I then save the result of the function (shown in the text box) to a record of my choice.

Thank in advance.

 

[Pat Hartman]

How can I then save the result of the function (shown in the text box) to a record of my choice.

This question is asked in one form or another EVERY day, usually more than once. The answer doesn't change. You should not be storing calculated values. Doing so violates third normal form and leads to data errors. Do your calculations in a query and use queries as the RecordSources for your forms and reports.

In the unlikely event that you truely need to store a calcualted value, use the following in the Form's BeforeUpdate event:

Me.SomeControl = some expression

 

[Chunk]

I have:

Product (prod_id, prodName, prodAge , [price_band_id])

PriceBand (price_band_id, price, rarity, region)

Then I have a form that allows the user to enter a new product. I only want to store the price_band_id next to the product, to prevent me storing the same combination of region, rarity and price over and over in the Product table.

The user selects a combination of rarity and region on the form, and the associated band should be automatically calculated by referencing the PriceBand table. I then want to store the price_band_id that is calculated, next to the product details that the user has just input into the form.

Do you think this is a bad way to do it? I thought it was in 3NF.

 

[The_Doc_Man]

Ah, we have a loose use of words. You asked a fair question but you didn't use the jargon we are used to. There is a difference between storing a computed value that is a pure formula vs. storing a value that allows you to find information in another table. This isn't a computation, it's a lookup, and yes, it is legal, moral, and proper. It is fully justified within 3NF.

But you might be re-inventing the wheel if you want to write your own function. Look up DLookup in the help files.

Also, you might wish to look at the possibility of defining a relationship between your product and price-band tables such that they could be joined using your price-band-id field. If you haven't already done so, that is...

 

[Chunk]

Also, you might wish to look at the possibility of defining a relationship between your product and price-band tables such that they could be joined using your price-band-id field. If you haven't already done so, that is...

I have. Do I use a different notation to you guys as well? The underlined field shows a PK and the [] shows a FK.

I need the form to look up the value of price band, depending on what the user selects for rarity and region.

I then need the result to be stored in the product form.

Sorry for any confusion.

 

[Pat Hartman]

A combo box would be appropriate. Look for posts on cascading combos since you have two dependant selection fields. Also take a look at Cascading combo example