Display entry of last record added in a field from a different table 1

[Aspen77]

Hello,

I am using the template Students DB provided by Microsoft. On the Form Student details, I would like to display a status field from another table. It seems that the form is bound to a query with one table, but when I add the second table to the query it creates many problems. Is it possible to display the field from the Student Attendance table without a query or relationship between the two different tables? This field is not for entry just display of status for the record being modified. Thank you!

 

[dhookom]

You could use code, a combo box, a subform, or DLookup(). The best solution depends on your table structures and data which we don't have any knowledge.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

The DLookup() Function that Duane mentioned is the usual method used when you're talking about retrieving a single Field from an Table that is not part of the Form's Record Source. There has to be a mutual Field between the two Tables, of course, such as a StudentID.

If StudentID is Numeric

Me.txtStatus = DLookup("Status", "StudentAttendanceTable", "[StudentID] = " & Me.txtStudentID)

If StudentID is Text

Me. txtStatus = DLookup("Status", "StudentAttendanceTable", "[StudentID] = '" & Me.txtStudentID & "'")

You'll have to replace txtStatus, Status, StudentAttendanceTable, StudentID and txtStudentID with the actual names of your Controls, Table and Fields, of course.

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[Aspen77]

Could you please assist me with the DLookUp function.

Guest Admittance Status tbl contains the field admittance status (this is the field that I want to look up).

On the main form, I select the guest which pulls from the Guest Attendance tbl. On the form, I created a textbox with the following function in the property sheet control source: =DLookUp("[Admittance Status]","Guest Admittance Status","[ID] = 4"). The function was just a test to see if I was using the function correctly.

It seems like it will work if I can figure out how to pull the last record of the guest admittance status for the guest I am currently viewing on the form. Please help! Thank you!

 

[dhookom]

Are there multiple records in the table [Guest Admittance Status] for ID? Can you share some information about the table such as fields, datatypes, and a few records?

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Yes, there are multiple records in the table [Guest Admittance Status] for ID.

Guest Admittance Status tbl
ID = autonumber
Guest= number
Admittance Status = text
Date Restricted = Date/Time
Date Restriction is Over = Date/Time
Restriction Reason = text

Records
11
Downy,Paul
Probation
11/11/12
11/30/12
Fighting

9
Downy,Paul
Do Not Return/No Trespass
12/05/12
blank
Drugs

6
Romero,Tanya
Probation
07/01/12
07/07/12
Fighting

Thank you for your assistance!

 

[dhookom]

What is the student field name in the main table? Is the student field related to the guest field in your lookup table?

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

What is the student field name in the main table? Is the student field related to the guest field in your lookup table?

The student field name in the main table is in two fields Last Name and First Name.

A query joins the main table student ID field with the Guest Admittance Status Student field.

 

[Aspen77]

I hope my last post made sense, but if not could you please look at the student DB template provided by Microsoft. Thanks again for your assistance.

 

[dhookom]

According to your previous posting, there is no Student field in the Guest Admittance Status table.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Sorry, I meant to say that a query joins the main table student ID field with the Guest Admittance Status Guest field.

 

[dhookom]

The DMax() function can find the most recent entry for the student in the Guest Admittance Status (GAS) table.

=DMax("[Date Restricted]","[Guest Admittance Status]","Guest=" & StudentID)

The status can be returned from DLookup() using the StudentID and the result of the DMax(). Something like this should work depending on if your table and field names are correct.

=DLookup("Admittance Status","[Guest Admittance Status]", "Guest=" & StudentID & " AND [Date Restricted]= #" & DMax("[Date Restricted]","[Guest Admittance Status]","Guest=" & StudentID) & "#")

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Thanks again for your assistance, it is almost there! I tried the code you provided but it did not fully work and I know you are providing me the code based on the information I am providing which appears not to be correct.

I tried the code =DMax("[Date Restricted]","[Guest Admittance Status]") without the criteria section and it returned the correct value(date of the last record).

I tried the code =DLookup("Admittance Status","[Guest Admittance Status]")without the criteria section and it returned a value in that field(not sure which one)

So from these results I know I am messing up with the criteria section. Listed below is more information on the tables and queries.
1. The Student table has fields ID, Last Name, First Name etc.
2. The Guest Admittance table has fields ID, Student (concatenated with the last name and first name from the Student Table)
3. The Guest Admittance Status Extended Query has fields Student Name(again concatenated), File As(concatenated), ID, Last Name First Name and when I dataview the query the link is students.id to Guest Admittance Status.Student.

So with this information would you be able to decipher what the criteria section should be? Thanks so much for your help!

 

[dhookom]

Your primary issue is the tables related to the Student table must store the primary key value from the Student table. This is the ID field. It makes no sense to store the concatenated last and first name from the Student table. Personally, IMO, I wouldn't go any further until the primary/foreign keys are correct.



Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Ok, thank you for all your help.

 

[Aspen77]

Hello - I finally got the following codes to work in separate text boxes:

=DMax("[Date Restricted]","[Guest Admittance Status]","Guest=" & [ID]) returns the latest date of Date Restricted
=DLookUp("[Admittance Status]","[Guest Admittance Status]","Guest=" & [ID]) returns the value but not the last one

When I tried to join the two so it would return the admittance status that matched the Date Restricted it gave an error. This is the code I tried

=DLookUp("Admittance Status","[Guest Admittance Status]","Guest=" & [ID] & " AND [Date Restricted]= #" & DMax("[Date Restricted]","[Guest Admittance Status]","Guest=" & [ID]) & "#")

I also tried to incorporate the DMAX text box value to the dlookup code but that didn't work. Please let me know if you see why the combine code does not work. Thank you so much for your help!

 

[Aspen77]

I got it to work! I still need to do more testing but with the initial testing it worked!

I found an article "Tips for Troubleshooting and Debugging DLookup() Expressions"

Press CTRL+G to open the Immediate window.
Try the function without any criteria. To do so, type the following in the Immediate window, and then press ENTER:

I tried many variations until I was able to get the right combination. Following is the code that worked:

=DLookUp("[Admittance Status]","Guest Admittance Status","[Guest]= " & [Forms]![Student Details]![ID] & " AND [Date Restricted]= #" & DMax("[Date Restricted]","[Guest Admittance Status]","[Guest]=" & [Forms]![Student Details]![ID]) & "#")

Thank you so much for all your help! I could not have done it without you! Please let me know how I can vote for you as one of the best programmers! Thanks again!

 

[dhookom]

Please let me know how I can vote for you as one of the best programmers!

Just check the links in these threads. I don't need or care to solicit votes but thanking someone suggests the thread has been successfully answered.

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Listed below is the code I am using. When a new record is opened it displays an error, how would you have it return a null if the field is blank?

=DLookUp("[Admittance Status]","Guest Admittance Status","[Guest]= " & [Forms]![Student Details]![ID] & " AND [Date Restricted]= #" & DMax("[Date Restricted]","[Guest Admittance Status]","[Guest]=" & [Forms]![Student Details]![ID]) & "#")

I tried the following but it did not work:
=Nz(DLookUp("[Admittance Status]","Guest Admittance Status","[Guest]= " & [Forms]![Student Details]![ID] & " AND [Date Restricted]= #" & DMax("[Date Restricted]","[Guest Admittance Status]","[Guest]=" & [Forms]![Student Details]![ID]) & "#"),0)

Any assistance would be greatly appreciated. Thank you!

 

[dhookom]

I believe you would need to use

 Nz([Forms]![Student Details]![ID],0)

Duane
Hook'D on Access
MS Access MVP