Select Price By Using Check Box 1

[jdaryl2003]

this is an update to a posting 705-1022476 I placed last week. Because of my limited knowledge I will try to simpifly my invoicing project. I will try this approach. On the Invoice header (Orders Table) I would like to put an option radio button or check boxes for selecting a price field. Price A and Price B.
On my “Orders Details” Subform Items from my “Products” table are entered. I will have two prices for each item in the product table. “Price A & Price B One of these prices will be inserted into the “UnitPrice” on the Orders Details form. Currently I am using the DLookUp: Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter) to insert the UnitPrice price from the Products table. This part works fine. From then on I am brain dead. So….How do I filter the selection for price A or B. Once again thanks for your help.

 

[TheAceMan1]

How are ya jdaryl2003 . . . . .

Right off the top, you would have somethig like:

[blue]Dim PriceAB

If Me![purple][b]OptionButtonA[/b][/purple] Then
   PriceAB = YourValue/TextBoxName [green]'for button A[/green]
ElseIf Me![purple][b]OptionButtonB[/b][/purple] Then
   PriceAB = YourValue/TextBoxName [green]'for button B[/green]
Else
   [green]'What ya wann do if no optionchecked![/green]
End If

Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

[green]'Where strFilter includes criteria for priceAB[/green]

[/blue]

See Ya! . . . . . .

 

[jdaryl2003]

Hi AceMan,
Thanks for your help. Please excuse my ignorance but do I place this code on the “Before Update” event on the UnitPrice field on Subform. Also, the names of my radio buttons are OptionButtonA & OptionButtonB. So does this go in the "YourValue/textBoxName" in the code
Thanks,

 

[willir]

Code would probably work best with the AfterUpdate event for the OptionGroup you create.

As an alternate approach, to save real estate, you could perhaps use a combo box to retrieve the prices for the selected product, and let the end user choose the price to use.

 

[TheAceMan1]

jdaryl2003 . . . .

I'm sure you wanna track which price was selected so try this:
[ol][li]In the Orders Table [blue]add two Yes/No fields.[/blue] Call them [blue]SelA & SelB.[/blue] Set the [blue]Default Value[/blue] for these to [blue]False[/blue].[/li]
[li]Open the [blue]Orders[/blue] form in design view. [blue]Drag/Drop the new fields[/blue] to the form header.[/li]
[li]In the [blue]AfterUpdate[/blue] event of [blue]SelA[/blue], copy/paste the following:

[blue]   If Me!SelA Then Me!SelB = False
   Call SetUnitprice
End Sub[/blue]

[/li]
[li]In the [blue]AfterUpdate[/blue] event of [blue]SelB[/blue], copy/paste the following:

[blue]   If Me!SelB Then Me!SelA = False
   Call SetUnitprice
End Sub[/blue]

[/li]
[li]In the code module of the Orders Form, [blue]copy/paste the following[/blue] routine ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Public Sub SetUnitprice()
   Dim sfrm As Form, PriceField As String, Criteria As String
   
   Set sfrm = Me![purple][b]DetailsSubFormName[/b][/purple].Form
   Criteria = "[[purple][b]ProductsPrimaryKeyName[/b][/purple]]=" & sfrm![purple][b]PrimaryKeyName[/b][/purple]
   
   If (Not Me!SelA) And (Not Me!SelB) Then
      sfrm!UnitPrice = "" [green]'No Selection![/green]
   Else
      If Me!SelA Then
         PriceField = "[[purple][b]PriceA_FieldName[/b][/purple]]"
      Else
         PriceField = "[[purple][b]PriceB_FieldName[/b][/purple]]"
      End If
   
      sfrm!UnitPrice = DLookup(PriceField, "Products", Criteria)
   End If

End Sub[/blue]

[/li][/ol]

[purple]Thats it . . . give it a whirl & let me know . . .[/purple]

See Ya! . . . . . .

 

[jdaryl2003]

Thanks theAceMan1 and Willir for your help. Sorry to say I am still at a standstill. As far as I know I’ve tried everything but my mind will not allow me to comprehend this problem. When I open up my “Orders” form and click on one of the two check boxes SelA or SelB I get an error message. Run-time error 3075 missing operator in query expression productID. If I chick “End” I can then enter product info and the price “Unitprice” which is the default "SelA" is correct and all seams to work fine. If I click “Debug” the code editor opens and sfrm unitprice dlookup UnitPrice products criteria is highlighted in yellow.

Here is a list important table, form and field names.

Orders table
Orders Details table
Products table
Orders Qry
Orders Form
Orders Subform Form
SelA Price field is UnitPrice
SelB Price field is PriceB


I posted the code below so please take a look and see what I am missing. Thanks.

Public Sub SetUnitPrice()
    Dim sfrm As Form, UnitPrice As String, Criteria As String
    
    Set sfrm = Me![Orders Subform].Form
    Criteria = "[ProductID] = " & sfrm!ProductID
    
    If (Not Me!SelA) And (Not Me!SelB) Then
    sfrm!UnitPrice = "" 'No selection
    
    Else
    If Me!SelA Then
    UnitPrice = "[UnitPrice]"
    
    Else
    UnitPrice = "[PriceB]"
    End If
    
    sfrm!UnitPrice = DLookup(UnitPrice, "(Products)", Criteria)
    End If
    
    End Sub