Background Info:
1) My employer only runs Access 97, a decade behind the times
2) I have a table (tblMedia) that has an AutoNumber primary key (intChunk), and two identifier fields (intRecipe, strType) along with other pertinent information. One [strRecipe] can have multipe [strType]s, with different values for the remaining fields for each [strType] of the [intRecipe]. I have other tables that I would like to use [intChunk] to reference the correct record in [tblMedia], rather than repeating the [intRecipe] and [strType] in each table.
3) I have a form for the user to enter data for one of the other tables, but since the [intChunk] is a meaningless identifier to them, I have unbound controls for them to enter values corresponding to [intRecipe] and [strType]. I have a hidden field on the form for [intChunk].
Problem:
After the user enters both the [intRecipe] and [strType], I would like to determine the appropriate [intChunk] based on the two fields, and have it populate the appropriate control on the form.
I have tried a bunch of things, including using VBA to run a SQL query, which easily handles the dual-field lookup, but I am unsure how to use the value of the only field returned (for ease of view, I have omitted all of the VBA punctuation).
SELECT tblMedia.[intChunk]
FROM tblMedia
WHERE tblMedia.[intRecipe] = intFormRecipe
AND tblMedia.[strType] = strFormType
I have been working on this for days, and it seems I am no closer to a solution. Maybe I am attacking this problem from the wrong directions. Any input would be appreciated.
Thanks!
Rehnahvah
1) My employer only runs Access 97, a decade behind the times
2) I have a table (tblMedia) that has an AutoNumber primary key (intChunk), and two identifier fields (intRecipe, strType) along with other pertinent information. One [strRecipe] can have multipe [strType]s, with different values for the remaining fields for each [strType] of the [intRecipe]. I have other tables that I would like to use [intChunk] to reference the correct record in [tblMedia], rather than repeating the [intRecipe] and [strType] in each table.
3) I have a form for the user to enter data for one of the other tables, but since the [intChunk] is a meaningless identifier to them, I have unbound controls for them to enter values corresponding to [intRecipe] and [strType]. I have a hidden field on the form for [intChunk].
Problem:
After the user enters both the [intRecipe] and [strType], I would like to determine the appropriate [intChunk] based on the two fields, and have it populate the appropriate control on the form.
I have tried a bunch of things, including using VBA to run a SQL query, which easily handles the dual-field lookup, but I am unsure how to use the value of the only field returned (for ease of view, I have omitted all of the VBA punctuation).
SELECT tblMedia.[intChunk]
FROM tblMedia
WHERE tblMedia.[intRecipe] = intFormRecipe
AND tblMedia.[strType] = strFormType
I have been working on this for days, and it seems I am no closer to a solution. Maybe I am attacking this problem from the wrong directions. Any input would be appreciated.
Thanks!
Rehnahvah
