modifiying a table using modules 2

[Sigmund]

i need to write a module that is assigned to a macro that will modify the following table

patientid dateofservice amountowed
123 1/2/00 $10.00
1/7/00 $15.00
1/15/00 $10.00
456 1/5/00 $10.00
1/8/00 $10.00
789 1/6/00 $30.00
1/9/00 $30.00

i need to be able to run a macro for the table that will input the patientid number in the other fields so that i can run the query for a total for each patient. I have never done this before and if someone can help i would it would be greatly appreciated.
thanx folks,
sigmund

 

[scroce]

maybe i am oversimplifying your need, but it seems to me that you can do this by using a parameter query, and either allowing the user to enter in the id number manually, or you can pull that entry from a combo, list or text box somewhere.

If you have tried this already, or know that it won't work, then disregard this, if you have not yet tried this, then post another response, and I'll be more specific.

SC.

 

[MichaelRed]

The below is "an approach". It has not been run/tested. Considering my (lack of) typing skills, there are probably typos as well as incomplete logic. You should make a backup of he table in question before even attemptin to do this. The concept is simple:

Assume the first record has a patient ID, so get an updataeable recordset of the table (rstTbl).

Grab the first patient ID

Start a loop through the table:

if the record does not have a patient Id, update it to the one we saved

otherwise (same as else)

replace the saved Patient Id with the one in the recordset.

Move to the next record.




Dim dbs as database
Dim rstTbl as Recordset
Dim LastPatId as String

Set dbs = Currentdb
Set rstTbl = dbs.openrecordset("YourTable", dbopendynaset)

LastPatId = rstTbl!PatientId

While Not rstTbl.EOF
[tab]If (isNull(rstTbl!PatientId) or rstTbl!PatientID = "&quot Then
[tab][tab]with rstTbl
[tab][tab][tab].Edit
[tab][tab][tab][tab]!PatientId = LastPatId
[tab][tab][tab].Update
[tab][tab]End With
Else
[tab]LastPatId = rstTbl!PatientID
End If
rstTbl.MoveNext
Wend



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over