Please help with Form & possible dlookup (1 Viewer)

I was a bit confusing but it is for the same form. I have the form "Locker Information" that has "Location,type,lockernumber,studentID,and Studentname" fields. When I enter the studentID I need it to check two things:

1)check to see if the studentID exist in the StudentInformation table, if not show a msg box "student doesn't exist in StudentInformation table" then open the StudentInformation table so the student can be added. This is the code you provided in your previous posting

2)I need it to check the LockerInformation table and if a record already exist with the same StudentID then show a msg box "student is already assigned a locker" then clear StudentIDtxtbox

Actually the code I provide is logically incorrect. The DCount() checks to see if a record exists with the particular studentID . If there is a studentID present (DCount>0) it updates, but that is not correct. What really needs to happen is if the studentID is not present (Dcount =0) then update the selected locker record with the studentID (the update query). If the Dcount returns a 1 which means the student already has a locker, the program should go to the statement after the ELSE, so that is the message we have to change

One thing you have not addressed is if the user attempts to assign a studentID to a locker that is already assigned to someone else. To prevent assigning the studentID to a locker that is already assigned, we should make sure we assign the studentID to a record that does not already have an ID (or in other words where StudentID is null).

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 & " AND StudentID is null"


currentdb.execute mySQL, dbfailonerror
msgbox "Update complete"


ELSE

msgbox "This student has already been assigned a locker"

DoCmd.OpenForm "FormNameHere", acNormal

END IF

DoCmd.Close acForm, "currentformname"

One thing you have not addressed is if the user attempts to assign a studentID to a locker that is already assigned to someone else. To prevent assigning the studentID to a locker that is already assigned, we should make sure we assign the studentID to a record that does not already have an ID (or in other words where StudentID is null).

Click to expand...

Regarding the above, no message is returned if the user attempts to assign a studentID to an already assigned locker. We would need an additional check and message to handle that if that is what you need.

This would be the code to also check to make sure the user does not try to assign an already assigned locker to a student.

IF DCount("*","StudentInformation", "location = '" & me.LocationCBO & "' AND " & lockertype='" & me.TypeCBO & "' AND [number]=" & me.NumberCBO & " AND StudentID is null")=1 THEN

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 & " AND StudentID is null"


currentdb.execute mySQL, dbfailonerror
msgbox "Update complete"


ELSE

me.studentIDtxtbox=null
msgbox "This student has already been assigned a locker"

DoCmd.OpenForm "FormNameHere", acNormal

END IF

DoCmd.Close acForm, "currentformname"

ELSE


msgbox "The locker selected is already assigned to someone else"

END IF

I tried playing with the code and came up with the following but it doesn't work:

If DCount("*", "StudentInformation", "StudentID=" & Me.StudentIDtxtbox) > 0 Then
If DCount("*", "LockerInformation", "StudentID=" & Me.StudentIDtxtbox) > 0 Then
DoCmd.OpenReport "Locker Infomation Report", acViewPreview
Else
mySQL = "Update LockerInformation set studentID=" & Me.StudentIDtxtbox & " WHERE location = '" & Me.LocationCBO & "' AND lockertype = '" & Me.TypeCBO & "' AND lockernumber = " & Me.NumberCBO & ""

CurrentDb.Execute mySQL, dbFailOnError
MsgBox "Update Complete1"
End If
Else
If DCount("*", "StudentInformation", "StudentID=" & Me.StudentIDtxtbox) = 0 Then
mySQL = "Update LockerInformation set studentID=" & Me.StudentIDtxtbox & " WHERE location = '" & Me.LocationCBO & "' AND lockertype = '" & Me.TypeCBO & "' AND lockernumber = " & Me.NumberCBO & " AND StudentID is null"

CurrentDb.Execute mySQL, dbFailOnError
MsgBox "The studentID you entered does not exist"
DoCmd.OpenForm "Student Maintenance", acNormal
Else
mySQL = "Update LockerInformation set studentID=" & Me.StudentIDtxtbox & " WHERE location = '" & Me.LocationCBO & "' AND lockertype = '" & Me.TypeCBO & "' AND lockernumber = " & Me.NumberCBO & ""

CurrentDb.Execute mySQL, dbFailOnError
MsgBox "Update Complete2"
End If
End If

If I finally have this right I need the following to happen when I press the "update student ID" command button on the form. (I uploaded a picture of the form"

1. If I enter a studentID that exist in the StudentInformation table & doesn't have a locker assigned, update the "LockerInformation" table & show msg"Update Complete"
2. if I enter a studentID that doesn't exist in the StudentInformation table, show a msgbox "student doesn't exist in system" then open "Student Maintenance" form
2. If I enter a studentID that already has a locker assigned in the "LockerInformation" table then, show msg"Student already has a locker assigned" then show what locker they are assigned in a form, report, msg box.etc.

Thank you. Do you accept paypal payments so I can send something for all of your time?

Would it be possible for you to post a copy of your database with any sensitive data removed or altered? It would be a lot easier to have a database to work with to troubleshoot the code.

BTW, I volunteer my time to assist others on the forum, so I do not expect or accept any payment.

Thank you. Here is a copy of the database. Any other constructive criticism of the database is accepted.

OK, I think I have the code doing as you described in your earlier post. The revised database is attached.


I took a quick look at your tables, and I do see some areas for improvement, but keep in mind that if we change the table structure, all your existing queries, forms and reports will not be of any value and will have to be recreated. Keep in mind also that having a sound table structure is critical to a successful application. You will have to make that decision. If you decide to rework the table structure, I am here to help.

Please try the following steps with the form "locker information" and see my notes: thank you

1)Select Location=Girls,Type=G5,Number=2. StudentID should come up blank until we assign a student: Now if i enter "studentID"=123456 and select the UpdateStudentID command button. then I get the "update complete" message and name appears
2)If i then go to girls, g5,3 and enters student id=123457 and select update I get the Update complete message but the name doesn't appear.
3)If i then go to girls, g5,4 and enter student ID=123457 which shouldn't work because it is assigned under girls, g5,2 and select update I get the "student already has a locker assigned" and it clears out "studentidtxt" box field. ***** I would like the message to to say "student already has a locker assigned "girls,g5,2"*****
4)If i go to girls,g5,2 and try to assign the locker to 987654 who doesn't have another locker yet, i get "the locker selected is already assigned to someone else; please select anothe locker. I want to be able to update the locker assignment. Possibilites would be Null because the student is moving or another student id because lockers were reassigned.

When I did item 2 as you described the name showed up just like it did with item 1. You could also make the student name appear by hitting the tab or enter key after entering the studentID number.

I took care of item 3 in the attached.

As to item 4. I would recommend a separate functionality to do that so as to not confuse your user. The form as you have it now is to assign lockers.

Just thinking how I would approach it, I would have a separate form with a single combo box that shows only assigned lockers. You would select a locker from the combo box and then click a button. That button would execute an update query to clear out the studentID field. You would do all of the de-assignments and then navigate the user to assign lockers using your other form. You should be able to create the update query since it would be similar to the update query in the code behind the button on the locker information form.

I know i have said this many times but thank you very much. Your help has been essential to this project.

the code worked well and i took your advice on item#4 but instead of creating another form i added a button on the same form and actually got it to work on my own. (of course i modified the code you suggested). I uploaded the database so you could see. Well in regards to this form i'm done. I posted help on a different forum so i can get my student information form working for the student picture. My form has the studentid, first name, last name, teacher, and an image control box. I have read many websites that offer different methods of linking the student pictures. We have about 3,000 students so i don't want to store the pics in the database. I also would like to avoid creating a field in the StudentInformatin table that needs to be manually updated with the location of the pictures. I want to store all of the pictures on \\server\share$\studentpictures\*StudentID.jpg*

Is it possible to code the form on current to load the picture of the current student being displayed based on the location of the picture i provided?

Thank you

Sorry forgot to attach the database

Unfortunately, pulling an image into a form is not something I've ever done before, so I would not be of much help there. I would suggest that you start a new thread with that specific topic. Someone on the forum has probably done something similar.

okay. sounds great. I will try that and once again thanks for all of your help.

You're welcome. Good luck on your project.

I'm not sure if you can still help me but i'm having a problem importing some data for the locker table. The teachers have a spreadsheet with "Location, LockerType, LockerNumber, and Serial#" which is the same format as my table but my table has all lockers available and their spreadsheet only has the lockers that are currently assigned. I'm trying to go live with the database and help them import the existing data so they don't have to manually input. I imported their information into a seperate table called "ABMLockerswithStudentIDs". It has the same table structure as the LockerInformation table. How can I run an update query to put the StudentID from the new table into the LockerInformation table? I have written a few update query and i can't get my syntax correct.

Create a query. Add both the imported data table and your lockerInformation table. Make joins between the two tables. If I recall, you may have to make a join for each of the fields (location, type and number). If so, the update query would look something like this

UPDATE ABMLockersWithStudentIDs INNER JOIN LockerInformation ON (ABMLockersWithStudentIDs.lockernumber = LockerInformation.LockerNumber) AND (ABMLockersWithStudentIDs.lockertype = LockerInformation.LockerType) AND (ABMLockersWithStudentIDs.location = LockerInformation.Location) SET LockerInformation.StudentID = [ABMLockersWithStudentIDs].[studentID];

However, I tried the above in the database you provided earlier but it kept giving me an error about key violations. I tried removing all of your indices but that didn't work either.

I do not recall a serial# field. Does that field uniquely identify each locker? That might allow us to overcome the above issues.