By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

How do I look search a table for value and populate another table with the value?

P: 24
I have 2 tables and a form. Table1 "PriceIndexWidget"
Table2 "HistoryWidget" and Form "HistoryWidget". I want to use a Checkbox on the form to populate a field value in the HistoryWidget table. I understand that when checkbox w001 is unchecked it = 0 and I populate field "me.widget001" value to 0 in table HistoryWidget. What I am trying to do is search the PriceIndexWidget table, field Part# for the value of widget001. Widget001 is Part# in table PriceIndexWidget. Then use it's value as the value for Me.Widget001 field on the form.

Can anyone give me some direction for developing the search code? I am not sure where to begin. I attached the database below. It is very basic but the logic will help me.

Code for Checkbox w001 on the form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub w001_Click()
  2. 'w001 = checkbox in form
  3. 'widget001 is field from tblHistoryWidget
  4. If w001 = 0 Then 
  5.     Me.widget001 = 0
  6. Else
  7.     <SOME SEARCH ROUTINE HERE>
  8.     Me.widget001 = some value
  9. End If
  10. End Sub
Expand|Select|Wrap|Line Numbers
  1. PriceIndexWidget Table
  2. Part#, text
  3. PartDescription, text
  4. PartCost, currency
Expand|Select|Wrap|Line Numbers
  1. HistoryWidget Table
  2. quote#,text
  3. widget001 ,currency
  4. widget002, currency
Attached Files
File Type: zip Database2.zip (26.5 KB, 46 views)
Dec 21 '11 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Your explanation seems to be circular. Where does the value that you want to use to do a search on come from?
Dec 21 '11 #2

P: 24
Basically what I am trying to do is give the user a checkbox form for a price sheet. For instance, the HistoryWidget table has quote#, and Widget001...999. If the quote # is P1234 and they select the Widget001 checkbox to True, I want to store the partcost for Widget001 in the Widget001 field in the HistoryWidget table. The point is each specific quote# could have a variety of different widget parts. So each particular quote# could have differing total values of widgets.

Widget001 is actually a part# with a fixed cost associated with it that is stored in table PriceIndexWidget. I know from the If-Then_Else logic I can store a value for Widget001 in the Else part of the code (let's save $1200), and that works fine. It stores $1200 in the Widget001 field (HistoryWidget table) if the checkbox is True or checked. I could do that if there were only 5 or 10 parts but there are hundreds. I would rather store the part#s, descriptions, and partcosts in a table (ie PriceIndexWidget) and do some type of look up in the table for the partcost associated with Widget001 or widget???. Am I making sense to you? I've been struggling on how to describe my problem that's why my posting is written so poorly, but I do know where I'm trying to go now. I read somewhere about DLookup, which sounds like it might work but I am unsure on how to the apply the logic/code.
Dec 21 '11 #3

NeoPa
Expert Mod 15k+
P: 31,186
Daryl, from the little I can make out of what you are trying to explain it seems to me you are trying to build up a record, for something akin to an order, in the [HistoryWidget] table (including data for both the order itself and also all the separate widgets). That, if true and I understand you remotely correctly, is a very bad way of trying to hold your data.

I still have very little idea of what you're working with. I respect that you've tried hard to explain it, and I'm sorry it makes so little sense to me, but I doubt it will turn out to be very important as my advice to you is to rethink your data design as a matter of priority, and when you build from that you should find things become somewhat more logical, and therefore easier to deal with.

Here is a link that should help you to do that Database Normalisation and Table Structures. You should probably end up with a table that handles the unifying item (equivalent to an Order Header) as well as one to handle each widget (equivalent to Order Details).

PS. As far as I can tell, you also failed to answer the one simple question I asked, but we probably won't need to worry about that. I was expecting a reference to some control on a form (as the answer), but we needn't pursue that unless/until it becomes necessary.
Dec 21 '11 #4

NeoPa
Expert Mod 15k+
P: 31,186
In case it's helpful (and certainly it doesn't mean you shouldn't also understand how to design your data better), I'll also include a link to an article that may help you with searching/filtering (Example Filtering on a Form). If you look through that and download the attachment and see for yourself how and why it works, you should find that understanding helpful I expect.
Dec 21 '11 #5

P: 24
NeoPa
Thanks for the advice I'll follow the link and review. You are correct though, I am trying to create a standard pricelist that has costing for every available part#, and these values seldom change in this list. I then want to create and track customized Order Details (ie build specific records) for each custom order. The reason I've used the Widgets example is that the data is garbage but the structure and code is the important part. You are also correct as I have been struggling with how best to hold all this data (ie separate tables, 1 table, what do the fields look like, etc).
Dec 21 '11 #6

NeoPa
Expert Mod 15k+
P: 31,186
Good luck with your learning then Daryl. Things always work better when your understanding develops I find ;-)
Dec 21 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.