grabbing data from table dynamically

[Crash1hd]

Ok how do I get this code to grab data from a table or query based on criteria?

Private Sub Level_AfterUpdate()
Enhance = Me.Enhancelist
Lvl = Me.Level
If Me.Enhancelist <> "" Then
Me.PriceTxt = Lvl
Me.StoreTxt = Enhance
End If
End Sub


so that instead of it saying <> "" it should say =

![tablename].... ????

I was also wondering I had 2 querys cause I couldnt get all the data from the 8 tables to go into 1 query as there is 42 table names and it kept telling me there was too many! arg very confusing

P.S. the tables are set up with 30 entrys each with a name and 42 field names the 30 names are in the enhancelist and the pricetxt is to come out where enhancelist equals one of the 30 entrys and then the text box would show the pricetxt

example table name store1

name| 1| 2| 3|
johne|100|150|200|
janes|150|100|120|

example table name store2

name| 1| 2| 3|
johne|200|150|200|
janes|250|100| 20|

and the from would show that out of the 2 tables that johne level 1 is better off at store2 and that janes level 3 is better at store 1 ext...

 

[suepowell]

To get data from a table to use in your VBA code you need to open a recordset and either step through for each line or open it with the correct line.

I am confused as to why you have so many table and field names, maybe you don't need a table for each store if the field names in each are the same, just have one extra field to define the store and repeat the lines with the values for each store.

This might make your queries easier.

If you need help with recordsets, let me know which version of access you are using as the code changed between versions 97 and 2000, and although the old code still works, theres no point showing you the old way if you are using acccess 2000 or later


Sue

 

[Crash1hd]

Im using access 2003! and as for all the tables well there are 7 stores in total each store will buy each item at a different price now there are 28 items which would be fine except that each item has 40 levels so in truth there are 1120 items in total IE item 1, item 2, item 40, itema 1, itema 2, itema 40 and I need to distinguish between each item and level #

 

[suepowell]

please forgive me if I have misunderstood you, but from the info you have supplied I would organise my tables something like this:

Table1 Stores

storeID (autonumber)
store address/ contact fields as required



table2 Items

ItemID (autonumber)
Itemname / description ect



table3 Levels

LevelID (autonumber)
LevelDescription etc



Table 4 Prices

PriceID (autonumber)
storeID
ItemID
LevelID
price


This gives you just 4 tables which describe the store, item, levels and prices paid for each store for each item at each level.
Therefore to add a new store or item you only need to add lines to existing tables, not setup new ones, and each store / item is only stored in 1 table, so if you need to change its details you only need to do it once.

You also get just 1 table holding all of your pricing information, so you can use this to give you the answers you want.

If this is confusing post a sample of your database, and I will see if I get show you what I mean with your own fields etc.

Sue

 

[Crash1hd]

Actually Yeah I didnt know where my head was but I figured something very similar lol see link

http://www.access-programmers.co.uk/forums/showthread.php?t=67378

SO pretty much the same as what you have the only difference is that each table has an autoID created by access so now what I am having trouble with getting it to grab the data in the form I have the form connected to the last table and I have the following code for the first dropdown

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!

 

[suepowell]

Hi

The code you have will only work for the record currently on view in the form.

To make a change for all the records in a table or query, you need to open a recordset, change the values as appropriate, and store them back to the table.

Is this actually what you want to do, or do you need to set the values of enhpriceselltxt and enhpricebuytxt for each record, depending on the values of Enhnamelist and Enhname And enhLevellist and EnhLevel that you can see on the form.

If you do need to loop through the whole table, I will sort out some sample code for you.

SUe

 

[Crash1hd]

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

 

[suepowell]

I think I see what you are trying to do now.

If I was doing this I think I would set up a little interogation form with 2 unbound combo boxes where the user could select item and level.
I would then have a button maybe labeled "get best price" and this button would open a report.

The datasource of the report would be a query based on the prices table, with the criteria of the item set to the item value selected on the form, and the criteria of the level set to the level value selected on the form.
The easiest way to get this right is to have the form open, and use the build button in the criteria line of the query, then just navigate to the form and select the correct combo box.

This would only return the prices for the item and level you wanted.

If you then sorted the prices ascending, the lowest price would be shown first, and you could put store details against price on your report, so if more than 1 store had the best price, you could see the list.

This would give you all stores, so you could compare the differences, maybe the store with the best price is further away or something.

If you really only want to show 1 store , or want to do it all on 1 form, you will need to use record sets to select and show the best price.

If you need to do this I will find some sample recordset code for you.


Hope this helps

Sue

 

[Crash1hd]

If you really only want to show 1 store , or want to do it all on 1 form, you will need to use record sets to select and show the best price.

If you need to do this I will find some sample recordset code for you.

That would be perfect as I am going to be duplicating the combo and txt boxs about 10 times so as to show 10 different items at once

 

[Crash1hd]

Continuous form is not what I am trying to acheave but interesting aproach what I need is 2 combo boxes that based on what is choosen goes through the database table and finds the Highest number using dmax and then puts it in the text fields without pressing any button, I dont want to be able to edit the data just view it. I would be doing this 10 times as there are 10 different items that I want to be able to check at any given time I think recordset is the approach I need however I am unable to find an example at this time

 

[suepowell]

I ahve attached a sample I created for someone who wanted to create their own autonumbering routine.

It shows the code for opening and closing a recordset in access 2000.

Once you have the recordset open, you can process the data however you like.

Hope this sets you on the right track

Sue

 

[Crash1hd]

I will see if that can help me any but here is a sample of the db that I am creating!

 

[suepowell]

I Think I see better what you are doing now.

I have made some suggestions in the sample

I have created a new table to hold the prices for all items at all levels in all stores.

I have created a query which will find the min price for each item at each level

I have created another query that takes the min prices and matches them up with the store name, so you get a list of stores with the minimum prices for each item at each level.

I have put this into a form which shows you all of the min prices for each item and level with the store name.

I know this output is not exactly what you want, and you need best selling and buying prices, but if you follow my logic in the queries, I am sure you can achieve what you actually want.

Good luck.

Sue

 

[Crash1hd]

Is there no way to get it to grab the highest price it works for the level 1 it just doesnt seem to go through the table when it is any other level then 1 as 1 is default? Thats why I figured there was a loop involed somehow? cause if you choose Damage as the name and make sure level 1 is already selected it will show the highest prices! check it out but if you where to choose level 2 since that doesnt mesh with the criteria it doesnt do it!

 

[suepowell]

I'm really confused by your last post.

If I look at the sample database and open the query I get the lowest price for levels 1 2 and 3 for product no 1. I only put 1 product in the prices table as an example. I just put a second product in the prices table and I get the answers I expect.

If you look at the results of qryminpricessotrenames (spelling mistake!) you will see 1 line for each item at each level, that I put in the prices table.

This should enable you to get easily at the data you want, even if you have to open this query using parameters from a form to get just the bits you want.

The form I have put in will now show you 6 lines, but I only put a little sample data in the table, as I don't quite know exaclty what you want to store.

How this sheds some light on the problem

Sue

 

[Crash1hd]

OK well in the form that I created if you choose level 1 then choose Damage in the name you will see that the 2 prices show the highest priced item! now with yours it does show the 3 lowwest pricves and there stores but I only need to show 1 at a time and just the highest price! you have to use the dropdown to find damage cause it wont autocomplete for somereason and make sure level 1 is selected (enhancments form)


See atached image

 

[Crash1hd]

Well I was able to figure it out 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