Dlookup Defaults

[Khartoum]

Hi,
I have an access form with:
ProductID
Product name
Product Price

When i select the product name from the combo, the productid and price populate as they should but on loading the form, i cannot get the default in the product ID and product price fields to default to blank / 0 - they disply 'error' in each box.
has anybody an idea what i need to do - i have tried on load events which keep debugging "you cannot add a default value to these lookup fields".

Any help would be appreciated

 

[NickHa]

What type of controls are 'Product Name' and 'Product Price'? What is their control source (i.e. are they bound to a field in a table or unbound)? If unbound, the values are presumably calculated somewhere and put into the controls?
Is '0' the value you want in these controls for a new record? What does '0' mean - is it the bound column of a multiple-column control (e.g. ComboBox) or the literal value (e.g. TextBox or displayed column of a ComboBox)? Probably the former from your description.
Have you looked at the 'Default Value' property for ComboBoxes?
If you provide more information about the three fields and their relationships, that would help.

 

[Khartoum]

Hey Nick,
The form is setout as:

ProdID Productname (Combo) ProductPrice as below:

ProductID - =DLookUp("[productid]","qryproddetails","[productid] =" & [Forms]![frmsales]![Prods])

ProductName is bound to Productname in tblproducts

ProductPrice is:
=DLookUp("[Productprice]","qryproddetails","[ProductID] =" & [Forms]![frmsales]![Prods])

On selection from productname combo - the ID and Price are populated
but on load the ID boxes show 'Error'. I added a blank record to the product table with an ID of 1 and productprice £0.00 and defaulted the combo boxes to " " but to no avail

Hope this helps Thanks John

 

[NickHa]

Is the form frmsales open when the code runs? What is the value in its field prods and is that value compatible with prodid?
Try the DLookup without the third parameter to see if the results change.

 

[Khartoum]

Yep, frmsales is the form with the boxes on - i removed the third parameter and it did still work but even though I have the default as "0 Sale" for the prods field, it is like it needs to refresh the prods field as when I drop down and then select "0 Sale", it populates the prodid field

 

[NickHa]

If I read this correctly, the two DLoopkUp statements are the row source for unbound controls? They both use a pre-defined query (qryproddetails) as their data source?
This being true, then the 'Error' message comes from the value of productid not being recognised when the form loads - it is only valid once the value is selected from the ProductName drop-down.
You say you want to get "0 Sale" as the default value, but I need to know more about what this string represents. Is "0 Sale" the name of a product in the tblproducts table, or are you representing the key (0) with the name (Sale). I assume it's the former, so what you actually need is the key value for "0 Sale" to be in the ProductID field when the form loads (or possibly when the current record is available. This leads to my next question: How is the combo box 'ProductName' defined? Specifically:

• What is its Row Source?
• What is the Bound column number?
• How many columns does the combo box contain (whether visible or not)?
• What is in each column (i.e. Data source)?

My suggestion is to put the key value for "0 Sales" in the combo box when the form's current event triggers - that way, you will have the default for every record. You can refine this by entering the default value only for new records - something like this:

Private Sub Form_Current()
If Me.NewRecord Then
  Rem set focus to the product name field
  Me.ProdName.SetFocus
  Rem set key value for default product name
  Me.ProdName=<key value for "0 Sales">
End If
End Sub

 

[Khartoum]

'0 Sale' is just a product in the product table and i slotted it inot the default properties of the combo box for the product name on the form therefore on load, in the product name field '0 sale' is there and therefore prodID '1' should appear which then gives a default price of £0.00 as below

ProdID [=DLookUp("[productid]","qryproddetails","[productid] =" & [Forms]![frmsales]![Prods])]

Product Name (prods) [combo looking at list of products in product table]

Product price [=DLookUp("[Productprice]","qryproddetails","[ProductID] =" & [Forms]![frmsales]![Prods])]

They work when I rechoose '0 sale' from the drop down but not on load even though it shows in the product name field on load. The code you have have given me would not work in this instance as it is setting focus but there are three Product name fields set as above to allow more products to be chosen on the sale form. They are called (prods2) and (prods3) so is there a way to run code to set value on all three product fields as '0 Sale'

thanks for your time on this

 

[NickHa]

No, that's not correct. if you have multiple fields, then set the focus to each one in turn, amend its value and move on to the next one. Your code would look like this:

Private Sub Form_Current()
If Me.NewRecord Then
  Rem set focus to the first product name control
  Me.Prods.SetFocus
  Rem set key value for default product name
  Me.Prods = 1
  Rem set focus to the second product name control
  Me.Prods2.SetFocus
  Rem set key value for default product name
  Me.Prods2 = 1
  Rem set focus to the third product name control
  Me.Prods3.SetFocus
  Rem set key value for default product name
  Me.Prods3 = 1
End If
End Sub

when you have set all the control values you want, you can move the focus to somewhere more useful for the user.
You can put this same code in the form's Load event (but without the check on NewRecord) if you want - it just seemed more approprite in the Current event to me.

 

[NickHa]

It just occurs to me that the default value you want in your drop-down boxes is always 1, so in the properties of each control for product (Prods, Prods2, Prods3), you can set the Default Value to 1.
That will have the same effect as the code and will solve your problem. That's a much neater solution, I think.

 

[Khartoum]

Thats great! worked a treat - what i dont get is why do we set the default to 1 in the product fields - is it because it is product id 1 in the products table, i'd have thought the default would have gone into the id field but hey many thanks for persevering
John

 

[NickHa]

what i dont get is why do we set the default to 1 in the product fields

The bound column of each Combo box is the product Id and the visible column is product name. When you change the combo box value in code (or set its default value), you use the bound column, which is different from what is shown in the drop-down list (although it can be the same thing in some cases).
The important point here is that record 1 of your products table has the product name of "0 Sales"; that is why you use 1 as the default value in this case.