Copying from non-null field in table to the next records below.

[cam2era]

Hello,

Our lab manager has given me an excel file with lab tests grouped by patient code. I've imported this into Access 2010.



However, the records for the labs do not repeat the patient code in each line. I've tried to create a macro that will use the "sendKeys" command to copy, then paste, the patient code in the field (field #1) for each record (stopping when reaching the next patient code).

It seems like this should be very doable, but I've been unsuccessful. There are many hundreds of labs (about 7 per patient), so I need to automate this. Thanks much.

[RuralGuy]

Will this idea work for you: http://www.mvps.org/access/forms/frm0012.htm

[ajetrumpet]

cam,

what's your data look like?? anything like the following??


PATIENT CODE LAB NUMBER

PATIENT CODE	LAB NUMBER
1	4
	5
	6
	7
	8
	89
	0
	8
	76
	5
	4
2	4
	56
	78
	8
	3
	2
	2
	2
	34
	5
3	5
	4
	5
	7
	8
	88

if it does look like that, put this function in access and run it:

function fillThoseMissingNums()

dim rs as dao.recordset
dim phold as long

set rs = currentdb.openrecordset("table name", dbopendynaset)

with rs
   .movelast
   .movefirst

   phold = ![Patient Code FieldName]

   .movenext

      do until .eof

         if isnull(![Patient Code FieldName]) then
            .edit
               ![Patient Code FieldName] = phold
            .update
         else
            if ![Patient Code FieldName] <> phold then
               phold = ![Patient Code FieldName]
            end if
         end if

   .movenext
      loop

end with

rs.close

set rs = nothing

end function

[cam2era]

Adam,

Yes, that's just how our lab table looks.

My experience with programming is a bit rudimentary. How would I set up the function? Would I put it in a window in the VBA editor? I'm pretty used to making buttons on forms that will invoke code in the editor (and macros), but no much beyond.

Thanks so much for your help!

Ernie (cam2era)

[ajetrumpet]

GO TO THE VB EDITOR, insert a new module and paste the code in

[cam2era]

Got it! It works great. Very elegant & it makes short work of making a very long list usable. Thanks again...

[ajetrumpet]

marked solved