Please help with Form & possible dlookup

[joses]

I have a table called "StudentInformation" that has studentid,firstname,lastname,teacherid and a second table called "LockerInformation" with location,type,number, and studentID. I have a form with 3 cascading combo boxes that currently works. The top level is location then type then number. I have a parameter query that i tied to each of the combo boxes and it returns a studentID if it exists in the table "LockerInformation" and allows me to input a studentID if it doesn't. I can't get my form to reflect this. I have tried a text box and a combo box but can't get a dlookup statement to work. I need it to look up the three values "Location", "Type", and "Number" from the "LockerInformation" table then return the student ID value. If the end user can see that it is blank then no student is assigned to the locker. In this case they would have the option of entering the studentID in the field. I would also like to display the student name in a seperate text box for accuracy of the end user. The name would be displayed "lastname,firstname"

 

[jzwp22]

...and allows me to input a studentID if it doesn't. I can't get my form to reflect this.

I'm not sure I understand what you mean by the form doesn't reflect this. Are you saying that when you enter the studentID for a locker that is not assigned, the combo boxes are not correspondingly updated with that student?

Is the form you are using bound to a table/query or is it unbound?

To display the student name in another textbox, after you enter an ID, you have to use some code in the after update event of the studentID textbox control. I would recommend creating a simple query with the student name formatted the way you want:

query name: qryStudentName
SELECT StudentInformation.StudentID, StudentInformation.lastname & ", " & StudentInformation.firstname as StudentName
FROM StudentInformation

me.studentnamecontrolname=Dlookup("StudentName","qryStudentName","StudentID=" & me.studentIDcontrolname )

 

[joses]

I tried the form unbound and bound to query but i couldn't get it to work. It is currently unbound and I get a #NAME? in the text box field when i open the form in form view.

So i can get my form with only the 3 cascading combo boxes to work. I have some queries that they reference and the code i'm attaching. When i add the text box for the studentID its unbound and i'm not sure what code to use for the control source. I have a paramater query called "Locker Information Student Query" that contains the following:

SELECT LockerInformation.Number
FROM LockerInformation
WHERE (((LockerInformation.Location)=[Forms]![Locker Information]![LocationCBO]) AND ((LockerInformation.LockerType)=[Forms]![Locker Information]![TypeCBO]))
ORDER BY LockerInformation.Number;

When i open the form and select "Location=Girls" "Type=G5" "Number=1" then open the query above it returns the StudentID"123456" which is in the LockerInformation table. I need that studentID to show in my form text box called StudentID. The next step would be for the end user to select (example) "Location=Girls" "Type=G5" "Number=5" which isn't currently assigned a studentID number so the form text box would be blank. The teacher would know its blank because the locker is available and the teacher can assign studentID"9999." The LockerInformation table should now have a StudentID of 9999 in the corresponding locker record.

Sorry, if i confused you. Thanks for any help you can provide.

 

[jzwp22]

I would probably recommend an unbound form; as such when you have identified an available locker, you will need to enter the studentID in your text box control and then run an append query to take the values from the 3 combo boxes and the studentID from the textbox control to append a new record to the table since those are the fields you mentioned in you initial post ( location,type,number, and studentID).

With respect to your combo boxes, why don't you filter out the used lockers so that the last combo box only shows the available ones?

 

[joses]

I tried using the unbound form but couldn't figure out how to make it work. Im not familiar with the append query. However, i need the studentID text box on my form to show me if there is a value in the table in case the teacher wants to know who is assigned to a locker. I also need to be able to enter a studentID in the text box if it is blank and the table get updated. Sorry, i hope im explaining it correctly.

"I would probably recommend an unbound form; as such when you have identified an available locker, you will need to enter the studentID in your text box control and then run an append query to take the values from the 3 combo boxes and the studentID from the textbox control to append a new record to the table since those are the fields you mentioned in you initial post ( location,type,number, and studentID)."

​

The teacher needs to see the status of all lockers.

With respect to your combo boxes, why don't you filter out the used lockers so that the last combo box only shows the available ones?
​

Thank you

 

[jzwp22]

i need the studentID text box on my form to show me if there is a value in the table in case the teacher wants to know who is assigned to a locker

You can do a couple of things.

1. In the after update event of the third combo box, push the selected studentID to the textbox me.textboxname=me.thirdcomboboxname.column(x) where x= the number of the column that holds the studentID. Access starts counting the columns at 0 not 1.

You can also use the same after update event to populate the student name in another textbox control on the form using the DLookup() function and the query I mentioned earlier & the studentID from the combo box as follow:

me.studentnametextbox=Dlookup("Dlookup("StudentName","q ryStudentName","StudentID=" & me.thirdcomboboxname.column(x))

2. You can adjust the row source of the third (locker) combo box to show the corresponding assigned student (if it is so assigned) that way you can reserve the textbox for inputting the student who need to be assigned a locker and your teachers will still be able to see the students assigned to each locker

To adjust the row source, you will need a query that joins the LockerInfomation table to the student table (via the studentID field). You will need to make the join a left join to show all lockers whether or not they have an assigned student. You will still have to filter that query based on the selection made in the previous combo box. Based the third combo box on this query.

 

[joses]

JZWP22--Thank you for all of your help. I actually feel good because i'm making progress. I did your option 1 for the studentID and it kind of worked. I can get the studentID to show up if it is assigned in the table but i can't change the StudentID in case the locker has been reassigned or put a StudentID in if it is not assigned. It allows me to type a studentId but it doesn't save in the "LockerInformation" table. I copied a pic of the form, and put some design info on a word document that i'm attaching. I'm pretty sure i can get the name so im focusing on the student ID part for now. Thank you again.

 

[joses]

sorry. i had the wrong attachment format

 

[jzwp22]

Since the form is unbound, it will never save the studentID you enter in the textbox. That is where the append query comes in. I would typically add a command button to the form and then execute the append query in code in the on click event of the button. You would pull info from the 3 combo boxes and the studentID entered in the text box and transfer that to the append query and execute it.

To help you out with that I will need to know what the bound field is of each of the three combo boxes and what datatype the corresponding field is of each in the locker info table. Also, I will need to know the datatype of the studentID field in the locker table.

 

[joses]

The Locker Information Table has the following fields and data types:

Location-Text
LockerType-Text
Number-Number, Long integer
StudentID-Number,Long integer

The bound field information for each item on the form is in the document I attached from my last post. Please let me know if I should upload again.

Thank you

 

[jzwp22]

I had not looked at your attachment, but I see that for two of the three combo boxes, you only have 1 field, so those are the bound fields. I assume that the [number] field in the last combo box is the bound field. BTW, the word number is a reserved word in Access, and it would be best not to use it as a table or field name. This link has a list of all of the reserved words.

What I typically do is to create the append query in code and then execute it. Values going into text field must be enclosed within single quotes. Dates must be enclosed within # signs (not an issue here). Values going into numeric fields do not need any delimiters.

Dim mySQL as string

mySQL="INSERT INTO LockerInformation (location, lockertype,number,studentID) VALUES ('" & me.LocationCBO & "','" & me.TypeCBO & "'," & me.NumberCBO & "," & me.studentIDtxtbox & ")"

currentDb.execute mySQL, dbfailonerror

 

[joses]

Thank you for your continued help. I tried the code you provided but it says that it would create duplicate values in my table. I believe since my table already includes records for each locker by (loc,type,number) and is missing the student ID I would need an update query. I looked up several examples but my syntax doesn't work.

I know my syntax is way off below, but I think this is something similar to what I need.

UPDATE LockerInformation SET StudentID=Me.StudentIDtxtbox WHERE Location=locationcbo & Type=Typecbo & Number=Numbercbo

 

[joses]

I thought I came up with the syntax to create an update query. I tied the query to a command button (on click) command but I get a data type mismatch in criteria expression error.

UPDATE LockerInformation SET LockerInformation.StudentID = "forms![Locker Information]![LocationCBO]"
WHERE (((LockerInformation.Location)='"[Forms]![Locker Information]![LocationCBO]"') AND ((LockerInformation.LockerType)='"[forms]![Locker Information]![TypeCBO]"') AND ((LockerInformation.LockerNumber)="[forms]![Locker Information]![NumberCBO]"));

 

[jzwp22]

Correct, if you have an existing record without the studentID then you would need an update query. My apologies for the misunderstanding. The code would look like this:

Dim mySQL as string

mySQL="Update LockerInformation set studentID=" & me.studentIDtxtbox WHERE location = '" & me.LocationCBO & "' AND " & lockertype='" & me.TypeCBO & "' AND [number]=" & me.NumberCBO

currentDb.execute mySQL, dbfailonerror

 

[joses]

I pasted the code and i get an expected end statement error at WHERE

I tried to insert spaces, commas, quotes to get it to work but had no luck. I appologize for being a pain in the a$$

 

[jzwp22]

I see I forgot an & and a double quote, my apologies

QL="Update LockerInformation set studentID=" & me.studentIDtxtbox & " WHERE location = '" & me.LocationCBO & "' AND " & lockertype='" & me.TypeCBO & "' AND [number]=" & me.NumberCBO

 

[joses]

It Works!!! Thank you... Of course now i'm on the next thing. I tried a student ID that doesn't exist and i get a debug error because the studentid doesn't exist in the StudentInformation Table. How can I create a message that shows up when the user enters a studentID that doesn't exist in the StudentInformation table? Perhaps "You have entered a Student ID that doens't exist in the database. Please use the Student Maintenace form to set up the student"

 

[jzwp22]

You can add embed the code I gave you in an IF..THEN...ELSE...END IF statement that tests whether a studentID exists, if it does, do the update. If it doesn't, open the appropriate form. (The following code has not been tested):

IF Dcount("*", "StudentInformation","StudentID=" & me.studentIDtxtbox) >0 THEN

mySQL="Update LockerInformation set studentID=" & me.studentIDtxtbox & " WHERE location = '" & me.LocationCBO & "' AND " & lockertype='" & me.TypeCBO & "' AND [number]=" & me.NumberCBO


currentdb.execute mySQL, dbfailonerror
msgbox "Update complete"


ELSE

msgbox "The studentID you entered does not exist"

DoCmd.OpenForm "FormNameHere", acNormal

END IF

DoCmd.Close acForm, "currentformname"

 

[joses]

I have to start by saying Thank you, Thank you, Thank you. You have been amazingly helpful and patient with me. I tried the code and it works great and of course there is always the but...

My table design for Locker Information doesn't allow for a student to have more than 1 locker. So now in my form that works great if the teacher enters an id that doesn't exist in student information provides a message and opens the student maintenace form.

However, if i enter a studentID that already has a locker assigned an error message comes up. I would like a message box to display "This student ID is already assigned another locker."

Once again, I know you are busy and have helped me way beyond what you should have already. If you have time to help with the alteration of the IF Else End if statement, i would really appreciate it.

 

[jzwp22]

So you are talking now about another form, not the one we have been working on, correct?