HOW TO SEARCH MULTIPLE FIELDS IN A RECORDSET(TABLE) THEN UPDATE ANOTHE

[Brianus]

Note: The tables was designed by a VB novice, so pardon the sloppiness.

inputtbl:
projid name hours
==== ==== ====
5555 John 20
5555 Bill 15

outputtbl:
projid name1 name2 hours1 hours2
==== ==== ==== ==== ====
5555 John Bill

Note: The field “hours1” belongs the person in field “name1”
And : The field “hours2” belongs the person in field “name2”

My objective is to do a search in outputbl with projid as the search key using “FindFirst” or any other search function. If I find the projid, and the name in any of the name fields also matches the name in inputtbl I will then update the “hours” field in the ouputtbl.
I hope I’m clear in my explanation
Any insight into the coding in VBA will be greatly appreciated.
BM.

 

[Golom]

Of course, with only two records, we could just brute force it but in a more practical situation (i.e. potentially hundreds or (shudder) thousands of records), there isn't really any way to do this simply.

Access is limited to tables or queries that have a maximum of 255 columns so you would run out of space after 127 distinct names in the table.

I suppose that you could open an Excel Object and load stuff cell-by-cell to get the display but there's little hope of using SQL to search for values because the table is so badly un-normalized.

You have selected a display model which, purely for display purposes, may work well with what you are doing. It is however, moderately disasterous for data manipulation (i.e. searching & updating) purposes in Access.