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

[Daryl Austin]

I have 2 tables and a form. Table1 "PriceIndexWidg et"
Table2 "HistoryWid get" 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.widget0 01" value to 0 in table HistoryWidget. What I am trying to do is search the PriceIndexWidge t table, field Part# for the value of widget001. Widget001 is Part# in table PriceIndexWidge t. 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.

Private Sub w001_Click()
'w001 = checkbox in form
'widget001 is field from tblHistoryWidget
If w001 = 0 Then 
    Me.widget001 = 0
Else
    <SOME SEARCH ROUTINE HERE>
    Me.widget001 = some value
End If
End Sub

[B][U]PriceIndexWidget Table[/U][/B]
Part#, text
PartDescription, text
PartCost, currency

[U][B]HistoryWidget Table[/B][/U]
quote#,text
widget001 ,currency
widget002, currency

[NeoPa]

Your explanation seems to be circular. Where does the value that you want to use to do a search on come from?

[Daryl Austin]

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 PriceIndexWidge t. 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 PriceIndexWidge t) 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.

[NeoPa]

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.

[NeoPa]

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.

[Daryl Austin]

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

[NeoPa]

Good luck with your learning then Daryl. Things always work better when your understanding develops I find ;-)