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.

 

[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.