not going through the table

[Crash1hd]

I am having trouble with getting it to grab the data in the form I have the form connected to the enhancement table and I have the following code for the first dropdown

there are 2 Combo Boxes the first one is Enhnamelist and the second is ENhLevelList I also have 2 txt fields named EnhPriceSellTxt, EnhPriceBuyTxt and based on the choices of the Combo Boxes the txt fields are filled in with the value that matches in the table (well actually I want it to go through the table and find the highest number in EnhPrice (field) and put that in EnhPriceSell

Private Sub EnhNameList_AfterUpdate()
    If Me.Enhnamelist = Me.Enhname And Me.enhLevellist = Me.EnhLevel Then
        Me.EnhPriceSellTxt = Me.EnhPricesell
        Me.EnhPriceBuyTxt = Me.EnhPricebuy
    Else
        Me.EnhPriceSellTxt = ""
        Me.EnhPriceBuyTxt = ""
    End If
End Sub

It doesnt seem to loop through the table at all so I know I am missing something!

 

[Robert_Mc]

Try using Domain functions to get the highest price; for example:

Me.EnhPriceSellTxt = _
DMax("enhpricesell","enhancement","enhname = enhnamelist")

Hope this helps

 

[Crash1hd]

That I dont think is where the problem is! Its Me.EnhLevel is not going past 1 in the loop

 

[Robert_Mc]

Hi

There is no looping here, from what you've said you are setting 2 values in combo boxes & returning values from a table into text boxes...

 

[Crash1hd]

Ok not quite what I am trying to do is as follows
I have a table that has the following Field Names

EnhStore, EnhLevel, EnhName, EnhPricesell, EnhPricebuy

Now I Have 2 combo boxes
the first one list Query and is named EnhNameList and is a list of names
the second one list another Query and is named EnhLevelList and is a list of levels I also have 3 text boxes one named EnhStoreTxt, EnhPriceSellTxt and the other named EnhPriceBuyTxt so when the user chooses a name and a level the form will return the Store and the best price to buy and sell based on the information in the table under EnhStore, EnhPricesell and EnhPriceBuy. so in the table if the data is as follows

EnhStore, EnhLevel, EnhName, EnhPricesell, EnhPricebuy
Store 1, null, 2, Juice, 200, Null
Store 2, null, 2, Juice, 100, Null
Store 3, null, 2, Juice, 140, Null
Store 4, null, 1, Juice, 200, Null
Store 4, null, 1, Pop, Null, 200

the txt boxes would return that for Name Juice the best deal is at store 1 for 200 ext...

OK so your code works but where my problem is now is that it is only grabbing the first Level of EnhLevel and it is not dynamically going through the table looking for the right number?

 

[Crash1hd]

Robert I finaly understood what you where talking about lol
THankyou

what I need was the following

and now it does all the proper checks!

If Me.Enhnamelist <> "" And Me.enhLevellist <> "" Then

'Price to Sell At
        Me.EnhPriceSellTxt = _
            DMax("enhpricesell", "enhancements", "enhname = '" & Enhnamelist & "' AND " & "enhlevel =" & enhLevellist)
        If Me.EnhPriceSellTxt <> "" Then
'Store to Sell At
            Me.EnhStoreSellTxt = _
                DMax("enhStore", "enhancements", "enhname = '" & Enhnamelist & "' AND " & " enhpriceSell =" & EnhPriceSellTxt & " AND " & "enhlevel =" & enhLevellist)
        Else
            Me.EnhPriceSellTxt = ""
            Me.EnhPriceBuyTxt = ""
    
            Me.EnhStoreSellTxt = ""
            Me.EnhStoreBuyTxt = ""
        End If

'Price to Buy At
        Me.EnhPriceBuyTxt = _
            DMin("enhpricebuy", "enhancements", "enhname = '" & Enhnamelist & "' AND " & "enhlevel =" & enhLevellist)
        If Me.EnhPriceBuyTxt <> "" Then
'Store to Buy At
            Me.EnhStoreBuyTxt = _
                DMax("enhStore", "enhancements", "enhname = '" & Enhnamelist & "' AND " & " enhpriceBuy =" & EnhPriceBuyTxt & " AND " & "enhlevel =" & enhLevellist)
        Else
            Me.EnhPriceSellTxt = ""
            Me.EnhPriceBuyTxt = ""
    
            Me.EnhStoreSellTxt = ""
            Me.EnhStoreBuyTxt = ""
        End If
    Else
        Me.EnhPriceSellTxt = ""
        Me.EnhPriceBuyTxt = ""
    
        Me.EnhStoreSellTxt = ""
        Me.EnhStoreBuyTxt = ""
    End If

Now I need to figure out how to change the storetxt to a combo box but that is for a new post see new post lol

http://www.access-programmers.co.uk/forums/showthread.php?p=286382#post286382

 

[Robert_Mc]

No problemo, glad to have been of some help....

Best of luck with your next post!