I was more into thinking about the retrieval technique (ADO, tables/queries, sql strings) and where those should be assigned, than solving the actual "how to get the correct information", based on the challenges of the syntax. Don't know quite where to start.
Now if the forms are designed, the controls on the forms have their controlsources set to field names that will exist in the recordset/recordsource there are a lot of techniques to use.
If the tables are within this database, or linked:
Create stored queries (with criteria/parameters if needed) and set that as the forms recordsourse (in design view).
Use the stored query (or table if applicable) as recordsource, and assign the where condition when opening the form:
[tt]docmd.openform "form1",,,"ID = " & me("combolID").value[/tt]
Assign the forms recordsource dynamicly based on some selection:
[tt]me.recordsource="select * from ....where ID =" & me("combolID").value[/tt]
You can also, for instance in a forms on open or load event, assign an ADO recordset to the form:
[tt]set me.recordset=rs[/tt]
Using Access 2000, the form recordset won't be updateable (well, it might be, with some quirks, I'm sure cmmrfrds knows more about that)
For the first issue, I'd place the combo on a form having table 1 as recordsource, and use the combo wizard to create a "find record" thingie. Code would look something like this in the after update of the combo:
[tt]dim rs as dao.recordset ' if dao
set rs=me.recordsetclone
rs.findfirst "id=" & me("combolID").value
if not rs.nomatch then me.bookmark=rs.bookmark
set rs=noting[/tt]
For ADO form recordset
[tt]dim rs as adodb.recordset
set rs=me.recordsetclone
rs.find "id=" & me("combolID").value
if not rs.eof then me.bookmark=rs.bookmark
set rs=noting[/tt]
or, as above, just use a where condition when opening the form, which implicates that the form needs table1 as recordsource:
[tt]docmd.openform "form1",,,"ID = " & me("combolID").value[/tt]
How would the first query look? That's really not my strength, but from what I see, you're going to have some problems due to the redundancy. I think I'd work on the structure a bit. That said, creating a query where the sequence of the fields mirrors which table they “belong” to, might perhaps be done thru a union query:
[tt]select ID, [Name], Yearsworked, YearRetired, Sex
from table2
where sex="F"
UNION
select ID, [Name], YearsStart, Title, Sex
from table3
where sex="F"[/tt]
Or to "spread" the columns a bit:
[tt]select ID, [Name], Yearsworked, YearRetired, "" as fld1, "" as fld2, Sex
from table2
where sex="F"
UNION
select ID, [Name], "" as fld3, "" as fld4, YearsStart, Title, Sex
from table3
where sex="F"[/tt]
To open this as a recordset, replace the double quotes with single quotes ('), and enclose the string in double quotes.
Don't know it this is what you're after, but this is what I'm able to do based on the information, and my assumptions.
Roy-Vidar
