Repeat previous value

[samia]

Hi Gurus,

I've a table visits in which am entering data...however I've searched for a way of having a value repeat to no avail. On one fo the forums (dont remember which) I got this

 IIF(IsNull(Height),DLOOKUP("Height","tblVisit","Height"),Height)

but it is not working. What I want is when I enter the patients height at first visit, it is repeated automatically for the other visits.

thank you in advance

Samia

 

[DCrake]

When you choose to add a new visit for a patient you need to do a search on all previous visits for that patient and get the last recorded height for them based on the visit date. If they have had many visits over a long period they could have grown during that time.

Me.Height = DLast("Height","Visits","PatientId=" & ID )

This is air code and as such replace my table and field names with yours.


David

 

[samia]

Thanks David for the first reply.

Should I discard my code? Where should your code come. My code is Height afterupdate. P'se also take a look at my code above, I dont know hwere the error is.

 

[DCrake]

You need to place this code in the Form_OnCurrent() Event

 

[samia]

place this code in the Form_OnCurrent() Event

OK done that, but my code still returns error. Can u help please...

Thank you so much

 

[DCrake]

What is the error? and what is the ACTUAL code you entered on the form event?

 

[samia]

Sorry David, I'll paraprase. Height is entered on the first visit with the other basic info which does not change. So for the subsequent visits, I dont want to keep re-entering the same value that was entered in 1st visit....I want it to just be picked from the tbl visits and filled for as long as the height doesnt change. here's my code:

 IIF(IsNull(Height),DLOOKUP("Height","tblVisit","Height"),Height)

hope I've made it clearer.

So it should be that for 2nd visit, after update if the height is not entered, chk tblvisits and use that value for field height if it is null.

Thank

 

[RuralGuy]

Is there an historical reason to keep the current height or will simply having the patients current height work for you?

 

[samia]

...or will simply having the patients current height work for you?

RuralGuy, it can do for me. I just thought like David mentioned above that if it changed at sometime in the future.

I appreciate your help

 

[RuralGuy]

The real question I was asking is do you need *many* records with the height or can you just use the height that was recorded during the first visit and change that value if need be loosing any history of the height changes?

 

[samia]

do you need *many* records with the height

Yes. Reason is I dont want to have null values in my db. i.e.

what I now have for tblvisits is:
Visit Date WT HT
1 02/04/09 49 147
2 09/04/09 47
3 16/04/09 50
4 23/04/09 49
5 30/04/09 48

I want when I loke at my tbvisits to see:
Visit Date WT HT
1 02/04/09 49 147
2 09/04/09 47 147
3 16/04/09 50 147
4 23/04/09 49 147
5 30/04/09 48 147

like it was manually entered.

Thanks

 

[RuralGuy]

The weight field I understand but why have the height field in that table at all? You can display and modify it with a query without having the extra field in that table.

 

[samia]

RuralGuy, am open to anything... How would I do this??

 

[RuralGuy]

Do you have both a "Patient" table and a "Visit" table? I assume the two tables are related 1:m with the PatientID field which is hopefully an AutoNumber field. Is there a "Height" field in the "Patient" table? The normal way to display the 1:m relationship is with a Form/SubFormControl scheme. Just display the height on the Form and the Visits on the SubFormControl. How are you doing it now?

 

[samia]

Yes, Ruralguy, I've both tables. Actually this was work that was done by someone else and I want to keep it the way it is. The field height is in the visit table, that why I get heights entered only for the 1st visit but not the other visits hence the code

IIF(IsNull(Height),DLOOKUP("Height","tblVisit","Height"),Height)

so that it would lookup the previous entry and fill it for the missing heights. The records are getting over 2000, so I cant make them re-enter all these records. I'm just looking for a way round the missing heights.

Thank you,

PS sorry was away for 4 days.

 

[RuralGuy]

I sort of took you down a path that we probably do not want to go. Sorry. Do you use a Form/SubForm to enter visit data or just a form of the Visit table?

 

[DCrake]

This line of code is logically incorrect:

IIF(IsNull(Height),DLOOKUP("Height","tblVisit","Height"),Height)

When using Dlookup there are three elements

1 What field to look for
2 Which table to look in
3 What to find in which field

Part three should be:

"PatientId =" & PatID

Thus the correct syntax is

Me.Height = Nz(Height),DLOOKUP("Height","tblVisit","PatientId=" & PatID))

Switching from IIF to Nz speads up the process. Don't forget I have used psudonames remember to change the field names to match yours.

David

 

[WIS]

Do you have both a "Patient" table and a "Visit" table? I assume the two tables are related 1:m with the PatientID field which is hopefully an AutoNumber field. Is there a "Height" field in the "Patient" table? The normal way to display the 1:m relationship is with a Form/SubFormControl scheme. Just display the height on the Form and the Visits on the SubFormControl. How are you doing it now?

Try this:

On the subform have a text box "Height" (Me.Parent!ControlName) where ControlName is the name of the "Height" text box on the main form.

 

[samia]

Thank you guys, Dcrake & Wis...I'm going to try out yo ways. Ruralguy, I've just a form for visit table.... meaning you enter patient info then click on a button and enter visit info.

Thank you all for trying to help me out.

Samia

 

[RuralGuy]

Thanks for the update. Let us know how you make out.