Retrieve Previous Record: DLookUp

[rodmc]

Hi Folks

Working on pulling through a record field (from a previous record) into a new record. Ive seen plenty of examples of this using DLookUp but they dont show what I actually want to achieve, so any help or pointers would be gratefully received.

Here's what I want to do. I have a table named tblRespite, in this table one of the fields is a memo field and rarely changes from record to record.
The tblRespite table is related to table tblUser. Each user may have one or more episodes of Respite. So a particular clients episodes of respite will no be in a numerical order within the tblRepsite and this is where Im having problems using DLookup. I want to pull this memo field from a particular users previous record and not THE previous record in the table as such (which may be another users record).
Ideally I thought a cmd button and the left of the form field that would copy the data from that particular users previous episode.
Looking at the examples Ive seen its all geared towards the record directly before and not a specific record.

Any ideas

 

[jzwp22]

Do you have a date associated with each respite episode that is recorded for each user?

 

[rodmc]

Do you have a date associated with each respite episode that is recorded for each user?

I certainly do! A start date and end date for each episode

 

[jzwp22]

OK, then you need to create a query that lists the most recent date for each user. I assume that two successive episodes' start/end dates for a particular user do not overlap, so you can use either date field, but once chosen, use it throughout.

SELECT userID, Max(yourdatefield) as MostRecent
FROM tblRespite
GROUP BY userID

Save the above query.


Now create another query that includes the above query and the tblRespite, join the two by both userID and the date fields. Select the userID, the date field and the memo field. Save the query.

You can now use this last query in your DLookup() function

DLookup("memofield","queryname","userID=" & [formcontrolthatholdsUserID] )

The above assumes that the userID is a numeric datatype field.

This will only display the memo field's content in a control on the form, you will not be able to edit it. Is that what you want?

 

[rodmc]

OK, then you need to create a query that lists the most recent date for each user. I assume that two successive episodes' start/end dates for a particular user do not overlap, so you can use either date field, but once chosen, use it throughout.

SELECT userID, Max(yourdatefield) as MostRecent
FROM tblRespite
GROUP BY userID

Save the above query.


Now create another query that includes the above query and the tblRespite, join the two by both userID and the date fields. Select the userID, the date field and the memo field. Save the query.

You can now use this last query in your DLookup() function

DLookup("memofield","queryname","userID=" & [formcontrolthatholdsUserID] )

The above assumes that the userID is a numeric datatype field.

This will only display the memo field's content in a control on the form, you will not be able to edit it. Is that what you want?

The UserID is a numeric field.
I have an example somewhere where the form has the Previous Record in one field and you click the command button to copy to the new record where it can be edited, the problem I have with this method is that I didnt want to show the field which contained the previous data as space is restricted on the form.

Youve certainly given me more ideas on how I can do this though

 

[jzwp22]

Of course you can use the DLookup() in code to do an update to an existing record or as part of an append query to create a new record.

Since you are just copying the text from episode to episode and in most cases not changing anything then why have the field associated with an episode rather than just have it associated with the user?

It may be more efficient to have a table that holds the related observations of a user and if you include a date field you can correlate it back to a particular episode if needed, but you would not have to copy it over and over again.

tblUserNotes
-pkUserNoteID primary key, autonumber
-fkUserID foreign key to user table
-dteNote (date of the note)
-memoNote

In terms of forms, you could display the notes in a tabbed subform or just have a button that opens the form when needed and old notes can be reviewed or new notes added.

 

[rodmc]

Thanks bud!

might have a go at doing this over the weekend!

 

[jzwp22]

You're welcome. Let us know how it works out.

 

[rodmc]

one last thing, If I make the prev record field invisible on the form, that wouldnt really present any major problems would it?

 

[jzwp22]

It should not cause a problem.

 

[rodmc]

Brilliant!

thanks again

 

[rodmc]

I put the DLookup in an unbound field in the form, unfortunately it keeps crashing access

 

[jzwp22]

Can you post the Dlookup() expression you used?

 

[rodmc]

=DLookUp("Q17","qryMRecent","SWIFTID=" & [txtSWIFTID])

 

[jzwp22]

What is the datatype of the SWIFTID field in the query (qryMRecent)?

I assume that txtSWIFTID is another control on the form and that it is a bound to a field of the form's recordsource?

Also are the controls for the dlookup() and the txtSWIFTID on the same form? One of them is not in a subform is it?

 

[rodmc]

What is the datatype of the SWIFTID field in the query (qryMRecent)?

I assume that txtSWIFTID is another control on the form and that it is a bound to a field of the form's recordsource?

Also are the controls for the dlookup() and the txtSWIFTID on the same form? One of them is not in a subform is it?

The data field (SWIFTID) is numeric and is bound to tblRespite and yes all the controls are on the same form

I tried using the full form path ie [Forms]![frmRespite]![txtSWIFTID] in the DLookUp as well withe the same result (Access crashes)

 

[jzwp22]

I'm not sure why Access is crashing. If something is wrong with the DLookup() it generally just shows #name or #error in the control on the form. Is there any chance that you could post the database with any sensitive data removed?

 

[rodmc]

no probs, theres only a few dummy records in it at present

I had put the unbound field under the label for Q17 on frmRespiteAdm but as I said it crashed and never saved the changes

 

[jzwp22]

The various fields Q17 through Q52 look like a series of questions/comments related to the person. Having sequentially numbered fields is a sign of repeating groups that indicates that the table is not normalized. For an overview of normalization, please check out this site.

The questions or things you are evaluating should be records in a separate table and then related to the respite event in another table with the appropriate responses.


tblEvaluationItems
-pkEvalItemID primary key, autonumber
-txtEvaluationItem

tblRespite
-RespID primary key, autonumber
-SWIFTID foreign key to other table
-RespStDte
-RespEndDte

tblRespiteEvaluation
-pkRespiteEvalID primary key, autonumber
-fkRespID foreign key to tblRespite
-fkEvalItemID foreign key to tblEvaluationItems
-Response

Now you have a wide variety of things you are evaluating and some can have multiple response so you have a one-to-many relationship which will require a different table structure. So the structure I have proposed above will not be appropriate for all the Q items in your table. Without knowing more about your application & your business rules, I'm not sure what else will be appropriate, so you will have to shed more light on that. For example, likes/dislikes/preferences are more directly attributable to the person and not the respite event.

I was looking at some of your other tables as well. For the medications, it would be better to have a table that just holds the medication names and then tie them to the person or the respite event for the person with a field to designate the dosage of that medication for that person/respite.


Also I would consider having 1 table for all people (users, emergency contacts, GP's). Then you can relate people to each other in another table.

Additionally, the 5 yes/no fields in tblUsers should be separate records in a related table. What would happen if you added a new item to check? You would have to alter your table structure and every related form, query or report--I wouldn't want to do that!

 

[rodmc]

Do you know they were in a seperate table called tblData and I ditched it as it seemed to represent a one to one relationship with tblRespite and Ive had problems with DLookup previously in 1 to 1 relations, hence the reason I ditched it