A
Aria
I am a new user to Access and this is my first database. I am trying to
create a database for my school with an emphasis on tracking keys. I am
tracking the keys individually. I know this is not the easiest database for a
beginner to create, but I cannot choose our circumstances. It is what it is
and I was hoping that someone could help me figure this out. Right now I am
on edge, extremely frustrated and completely stuck. I have been trying to
figure this out for a week. Maybe I belong in the new user section but I
thought I’d try here first since I don’t know where I’m going wrong. I’ve
received invaluable help in this newsgroup. I was given code for the forms I
am trying to create but I can’t seem to get the results that I’m seeking. I
don’t know what information you will need so I’m sorry if this is too long.
I have 2 forms, form Keys with sub form Locks and form Locks with sub form
Keys. It might sound a little strange but it’s exactly what I need. We have
permanent site staff (teachers, custodians, clerical, etc.) and temporary
employees (substitute teachers, custodians, etc.) Staff may come to me and
tell me they need a certain key. The way it should work in the end is that I
go to form keys, use the unbound combo box to look for the key and it will
tell me the location. Maybe I should also mention that we are spread out over
more than one campus and I have to track, not only room keys, but also
stadium, storage, gate, wing and master keys. It will work in reverse for
form Locks. I know a location or someone tells me the location and I use the
drop-down combo to find the location via the form. The sub form then
populates all the keys for that location, indicates whether it is a master
key, and if it is available for assignment and whom it is currently assigned
to. I tried to check everything I could more than once but since it still
isn’t working, I’m hoping you see something I missed. BTW, I did get it to
work for about 2 minutes until I changed the Row Source FindAKey, KeyCode to
sort ascending because it was all mixed together. When it stopped working I
changed it back, but no luck. I'm sorry if this is too much information.
Thank you for any assistance you can provide.
In form Keys:
The Record Source is qryKeys.
The SQL is:
SELECT tblKeys.KeyID, tblKeys.KeyCode, tblKeys.MstrKey, tblKeys.InService,
tblKeys.Comments, tblKeys.Available
FROM tblKeys;
Row Source:
SELECT [tblKeys].[KeyID], [tblKeys].[KeyCode] FROM tblKeys ORDER BY
[tblKeys].[KeyCode];
There is an event procedure After Update which states the following:
Private Sub cboFindKey_AfterUpdate()
With Me.RecordsetClone
.FindFirst "KeyID=" & Me.cboFindKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.lstAssignedTo.Requery
End Sub
In the sub form, Key ID links Child field and Master/Parent field.
CboSelectLock has Lock ID as the Control Source.
The Row Source is:
SELECT [tblLocks].[LockID], [tblLocks].[Location],
[tblBuildings].[BuildingDescription] FROM tblBuildings INNER JOIN tblLocks ON
[tblBuildings].[BuildingID]=[tblLocks].[BuildingID];
SQL:
SELECT tblLocks.LockID, tblLocks.Location, tblBuildings.BuildingDescription
FROM tblBuildings INNER JOIN tblLocks ON tblBuildings.BuildingID =
tblLocks.BuildingID;
Column Count = 4
Column Width = 0";1.5";0";0"
FrmLocks
CboFindALock, unbound
Row Source:
SELECT [tblLocks].[LockID], [tblLocks].[Location],
[tblBuildings].[BuildingDescription] FROM tblBuildings INNER JOIN tblLocks ON
[tblBuildings].[BuildingID]=[tblLocks].[BuildingID] ORDER BY
[tblLocks].[Location];
SQL
SELECT tblLocks.LockID, tblLocks.Location, tblBuildings.BuildingDescription
FROM tblBuildings INNER JOIN tblLocks ON tblBuildings.BuildingID =
tblLocks.BuildingID
ORDER BY tblLocks.Location;
Sub form Keys Assigned to this Lock
cboSelectAKey, unbound
Row Source:
SELECT [tblKeys].[KeyID], [tblKeys].[KeyCode], [tblKeys].[MstrKey] AS Expr1,
[tblKeys].[InService] FROM tblKeys;
create a database for my school with an emphasis on tracking keys. I am
tracking the keys individually. I know this is not the easiest database for a
beginner to create, but I cannot choose our circumstances. It is what it is
and I was hoping that someone could help me figure this out. Right now I am
on edge, extremely frustrated and completely stuck. I have been trying to
figure this out for a week. Maybe I belong in the new user section but I
thought I’d try here first since I don’t know where I’m going wrong. I’ve
received invaluable help in this newsgroup. I was given code for the forms I
am trying to create but I can’t seem to get the results that I’m seeking. I
don’t know what information you will need so I’m sorry if this is too long.
I have 2 forms, form Keys with sub form Locks and form Locks with sub form
Keys. It might sound a little strange but it’s exactly what I need. We have
permanent site staff (teachers, custodians, clerical, etc.) and temporary
employees (substitute teachers, custodians, etc.) Staff may come to me and
tell me they need a certain key. The way it should work in the end is that I
go to form keys, use the unbound combo box to look for the key and it will
tell me the location. Maybe I should also mention that we are spread out over
more than one campus and I have to track, not only room keys, but also
stadium, storage, gate, wing and master keys. It will work in reverse for
form Locks. I know a location or someone tells me the location and I use the
drop-down combo to find the location via the form. The sub form then
populates all the keys for that location, indicates whether it is a master
key, and if it is available for assignment and whom it is currently assigned
to. I tried to check everything I could more than once but since it still
isn’t working, I’m hoping you see something I missed. BTW, I did get it to
work for about 2 minutes until I changed the Row Source FindAKey, KeyCode to
sort ascending because it was all mixed together. When it stopped working I
changed it back, but no luck. I'm sorry if this is too much information.
Thank you for any assistance you can provide.
In form Keys:
The Record Source is qryKeys.
The SQL is:
SELECT tblKeys.KeyID, tblKeys.KeyCode, tblKeys.MstrKey, tblKeys.InService,
tblKeys.Comments, tblKeys.Available
FROM tblKeys;
Row Source:
SELECT [tblKeys].[KeyID], [tblKeys].[KeyCode] FROM tblKeys ORDER BY
[tblKeys].[KeyCode];
There is an event procedure After Update which states the following:
Private Sub cboFindKey_AfterUpdate()
With Me.RecordsetClone
.FindFirst "KeyID=" & Me.cboFindKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Me.lstAssignedTo.Requery
End Sub
In the sub form, Key ID links Child field and Master/Parent field.
CboSelectLock has Lock ID as the Control Source.
The Row Source is:
SELECT [tblLocks].[LockID], [tblLocks].[Location],
[tblBuildings].[BuildingDescription] FROM tblBuildings INNER JOIN tblLocks ON
[tblBuildings].[BuildingID]=[tblLocks].[BuildingID];
SQL:
SELECT tblLocks.LockID, tblLocks.Location, tblBuildings.BuildingDescription
FROM tblBuildings INNER JOIN tblLocks ON tblBuildings.BuildingID =
tblLocks.BuildingID;
Column Count = 4
Column Width = 0";1.5";0";0"
FrmLocks
CboFindALock, unbound
Row Source:
SELECT [tblLocks].[LockID], [tblLocks].[Location],
[tblBuildings].[BuildingDescription] FROM tblBuildings INNER JOIN tblLocks ON
[tblBuildings].[BuildingID]=[tblLocks].[BuildingID] ORDER BY
[tblLocks].[Location];
SQL
SELECT tblLocks.LockID, tblLocks.Location, tblBuildings.BuildingDescription
FROM tblBuildings INNER JOIN tblLocks ON tblBuildings.BuildingID =
tblLocks.BuildingID
ORDER BY tblLocks.Location;
Sub form Keys Assigned to this Lock
cboSelectAKey, unbound
Row Source:
SELECT [tblKeys].[KeyID], [tblKeys].[KeyCode], [tblKeys].[MstrKey] AS Expr1,
[tblKeys].[InService] FROM tblKeys;