UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Only Display Available Records, Access 2013    
 
   
vanzie
post Sep 11 2019, 05:51 AM
Post#1



Posts: 9
Joined: 29-January 19



Hi everyone

If there is any similar topic, please direct me to it.

Im designing a database where we use firearms. In this DB, you have the officers who will book out a firearm and then you get the DFO (Designated Firearms Officers) who will handle which firearm will be assigned to which officer.

I have a separate table with all the firearms info such as make, model, serial etc. And another table with the info of the officers with a field "Assigned Firearm".

If a officer gets registered by the DFO, they should assign only the firearm serial to that officer. What i want to do is that if another officer should get registered, the new officer should not be assigned with the same serial number. Is there a way to hide the serial from the combo box if it has already been assigned?

Any help will be appreciated

Thank you
This post has been edited by vanzie: Sep 11 2019, 05:52 AM
Go to the top of the page
 
Larry Larsen
post Sep 11 2019, 07:06 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,364
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

It would be of interest to us to see what you have that populates the combo..

My first thought would be around adding another field to your table, so when an officer gets assigned a serial number you also update this added field..

The field would be of a "tick box" type of object field. (eg: True/False)

So then using this field, I could eliminate those serial numbers that have already be assigned and the combo list would/will "only" show available serial numbers..

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
projecttoday
post Sep 11 2019, 07:23 AM
Post#3


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


If you have a separate officers/firearms table then you need to base the combo box for selecting the firearm on an unmatched query between the assigned firearms table and the firearms table.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 11 2019, 07:32 AM
Post#4


UtterAccess VIP
Posts: 7,968
Joined: 24-May 10
From: Downeast Maine


To add a little to what Larry wrote, it would be possible (and not especially difficult) to mark the Yes/No field at the same time the serial number is assigned to an officer. It would also be necessary to handle changes such as an officer retiring or otherwise leaving the department. Maybe mark the officer as inactive, and immediately loop through the serial numbers assigned to that officer and change the Yes/No field. I won't try to cover the various other contingencies that may occur, such as a firearm being retired and the officer issued a new one, but you should consider all of the possibilities that would lead to a serial number change.
Go to the top of the page
 
vanzie
post Sep 11 2019, 07:42 AM
Post#5



Posts: 9
Joined: 29-January 19



The tables in my DB is tblFirearms with the information for all available firearms in the safe. Then there is tblOfficers with a field for AssignFirearm.

My form frmAddOfficer is unbound and requires the name, surname, personnel number etc. of each officer. At the bottom of the form is two unbound combo boxes with its row source linked to a query named FirearmsExt. The one combo box value is dependent from the selection of the previous CB.

My save button VBA will AddNew to the DAO.Recordset and save all the values to tblOfficers.

So somewhere i need to make sure i have a way to let my tblFirearms indicate that the serial number assigned is not available for the next new registration.

Go to the top of the page
 
nvogel
post Sep 11 2019, 08:19 AM
Post#6



Posts: 1,011
Joined: 26-January 14
From: London, UK


If the rule is that a firearm can only be assigned to one officer at a time then it seems to make sense to put the assignments into a separate table. Like this:

CREATE TABLE AssignedFirearm
(SerialNum INT NOT NULL PRIMARY KEY REFERENCES Firearm (SerialNum),
BadgeNum INT NOT NULL REFERENCES Officer (BadgeNum));

The key on SerialNum ensures that a firearm can only be assigned to one officer at a time. Changing the UI is still important of course, but you can use the database rather than the UI to enforce the rule.
Go to the top of the page
 
GroverParkGeorge
post Sep 11 2019, 08:48 AM
Post#7


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


As a general rule, it makes sense to implement constraints in the database rather than the user interface. One reason for that, of course, is that doing so means the rules are enforced regardless of adaptations to the UI. Also, in Access, we know that users can potentially bypass an interface. It's not desirable, but it can happen.

I'm curious, though, about one aspect of this. Given what we see here so far, you can store the CURRENT firearm assignment for each officer with this table design. I wonder if you need or want a history of assignments. In other words, would you need or want to know that Officer Richard Tracy had Firearm 123456 on 9/11/2019 and a different Firearm, 876543, on 9/10/2019. Or is it the case that each assignment is permanent for the given officer and firearm?

If you do need a history, you would want to consider adding an "Effective Date" field to that Junction table to handle those multiple, consecutive assignments.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post Sep 11 2019, 11:14 AM
Post#8



Posts: 3,331
Joined: 27-February 09



Nick,
It would seem that a firearm can be issued to more than one Officer over time, so wouldn't it be something like

CREATE TABLE Loan (
FirearmID INT NOT NULL,
OfficerID INT NOT NULL,
IssueDate DATE NOT NULL,
ReturnDate DATE
);

Then you'd need a query to find all the "available" firearms - with no Loans on the current date? (Show only those firearms that have either never been issued or where there does not exist an unreturned loan.)

Right?
Go to the top of the page
 
projecttoday
post Sep 11 2019, 11:18 AM
Post#9


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


It is likely that you will have several of each type of gun. I think it is a good idea to have a firearms table that stores all the models. This table does not point at any actual guns. That would go in another table which would have the firearm id and the serial number of the specific gun in stock. Oh, and a manufacturers table would be good, too.

--------------------
Robert Crouser
Go to the top of the page
 
projecttoday
post Sep 11 2019, 11:21 AM
Post#10


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


If you don't need to maintain a trail of who was assigned what and a gun can be assigned to only one officer at a time then all you need is the officer id in the table, the table for the actual guns, that is.

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Sep 11 2019, 11:33 AM
Post#11



Posts: 3,331
Joined: 27-February 09



This design will work... Used it a long time ago with computer stuff, but the idea is exactly the same:

GunModel--(1,M)---Gun---(1,M)---Loan---(M,1)---Officer

If you do it this way, the Loan table is something like

CREATE TABLE Loan (Loan ID INT IDENTITY,
GunID INT NOT NULL,
OfficerID INT NOT NULL,
IssueDate DATE NOT NULL,
ReturnDate DATE
FOREIGN KEY GunID REFERENCES Gun(GunID),
FOREIGN KEY OfficerID REFERENCES Officer(OfficerID));



This post has been edited by MadPiet: Sep 11 2019, 11:42 AM
Go to the top of the page
 
projecttoday
post Sep 11 2019, 12:09 PM
Post#12


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


I have a program written in Visual Basic .Net.

--------------------
Robert Crouser
Go to the top of the page
 
vanzie
post Sep 12 2019, 12:08 AM
Post#13



Posts: 9
Joined: 29-January 19



For the purpose of the DB, it will be used for when an officer comes to the safe, selects his/her name on the PreBookout form (purpose of the pre bookout is to check whether their competency is still valid or have expired. If expired and they have updated their competency then the DFO has an override option) and upon clicking the bookout button, his/her details transfers over to the actual bookout form where the officer signs for the firearm. Each officer will be assigned with a firearm permanently.

I would upload the current DB im working on but its over 2mb in size.

Would it be best to add a field to my firearms table that indicates whether a firearm has been assigned (yes/no)?

Heres my code for when an officer gets registered:

Dim dbUser As DAO.Database
Dim UserRec As DAO.Recordset
Set dbUser = CurrentDb
Set UserRec = dbUser.OpenRecordset("tblOfficers")

UserRec.AddNew
UserRec("FirstName").Value = Me.txtFirstName
UserRec("LastName").Value = Me.txtLastName
UserRec("EmpNumber").Value = Me.txtEmpNr
UserRec("EmpEmail").Value = Me.txtEmpEmail
UserRec("Competent").Value = Me.cboCompetent
UserRec("IssuedDate").Value = Me.txtIssuedDate
UserRec("ActivityDate").Value = Now
UserRec("LastActivity").Value = "Officer Added"
UserRec("AssignedFirearm").Value = Me.cboFirearmSerial
UserRec.Update

Can i perhaps link the two tables and say that in firearms table, the firearm ID (auto) is linked to officers table AssignedFirearm field?
Go to the top of the page
 
vanzie
post Sep 12 2019, 12:29 AM
Post#14



Posts: 9
Joined: 29-January 19



Okay ive tested something:

On my firearms table i have added a field Assigned with yes/no type

In my FirearmsExt query the criteria is set to false. Ive ticked off 2 firearms assigned to an officer for example there's two CZ75 types and both have been assigned

When i went to add an officer, i wanted to select the CZ75 in the FirearmsType combo but it did not show in the list. I went back and deselected it from the assigned field in the firearms table and it showed up again when i went back to the add officer form.

Given the code i added previously, ive DIM the firearms table as a recordset2 and upon saving a new officer, it did mark the assigned field as true but i need something to refer the selected firearm to that specific record and mark that firearm assigned as true and not just add a new record to the firearms table. Any idea?

Dim dbUser As DAO.Database
Dim UserRec As DAO.Recordset
Dim UserRec2 As DAO.Recordset2
Set dbUser = CurrentDb
Set UserRec = dbUser.OpenRecordset("tblOfficers")
Set UserRec2 = dbUser.OpenRecordset("tblFirearms")

UserRec.AddNew
UserRec("FirstName").Value = Me.txtFirstName
UserRec("LastName").Value = Me.txtLastName
UserRec("EmpNumber").Value = Me.txtEmpNr
UserRec("EmpEmail").Value = Me.txtEmpEmail
UserRec("Competent").Value = Me.cboCompetent
UserRec("IssuedDate").Value = Me.txtIssuedDate
UserRec("ActivityDate").Value = Now
UserRec("LastActivity").Value = "Officer Added"
UserRec("AssignedFirearm").Value = Me.cboFirearmType & " " & Me.cboFirearmSerial
UserRec.Update

UserRec2.Edit
>>Somewhere here must be some code to refer the AssignedFirearm above to edit and update the record in the Firearms table<<
UserRec2("Assigned").Value = "True"
UserRec2.Update
This post has been edited by vanzie: Sep 12 2019, 12:35 AM
Go to the top of the page
 
vanzie
post Sep 12 2019, 01:39 AM
Post#15



Posts: 9
Joined: 29-January 19



Here is my final code when clicking save:

Dim dbUser As DAO.Database
Dim UserRec As DAO.Recordset
Set dbUser = CurrentDb
Set UserRec = dbUser.OpenRecordset("tblOfficers")

UserRec.AddNew
UserRec("FirstName").Value = Me.txtFirstName
UserRec("LastName").Value = Me.txtLastName
UserRec("EmpNumber").Value = Me.txtEmpNr
UserRec("EmpEmail").Value = Me.txtEmpEmail
UserRec("Competent").Value = Me.cboCompetent
UserRec("IssuedDate").Value = Me.txtIssuedDate
UserRec("ActivityDate").Value = Now
UserRec("LastActivity").Value = "Officer Added"
UserRec("AssignedFirearm").Value = Me.cboFirearmType & " " & Me.cboFirearmSerial
UserRec.Update

Dim strSQL As String
strSQL = "UPDATE tblFirearms SET Assigned = '1' WHERE FirearmSerial = " & """" & Me.cboFirearmSerial.Value & """"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
CurrentDb.Execute strSQL, dbFailOnError

Tested many times and it works great. If there are any errors please let me know so i dont run into future trouble

Thanks for all the help

Go to the top of the page
 
projecttoday
post Sep 12 2019, 06:43 AM
Post#16


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


Looks like you're using unbound forms. Why do you use recordset code for the officer and SQL code for the firearm? Is there validation code somewhere? What happens if you want to register an officer but not assign a firearm? Or assign a firearm to an already-registered officer? Are you sure this is working correctly? It looks like you're running the firearm code twice.

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 12 2019, 12:28 PM
Post#17


UtterAccess VIP
Posts: 7,968
Joined: 24-May 10
From: Downeast Maine


If you are using Access for the data tables, if Assigned is a True/False field the value is True or False, or -1 or 0, without quotes. Access can often sort it out, but should be be made to do so.
Go to the top of the page
 
vanzie
post Sep 13 2019, 12:34 AM
Post#18



Posts: 9
Joined: 29-January 19



@projecttoday most of my DB i design i use unbound forms with recordset VBA. If there is any recordset codes i can use to reference the field in the firearms table that needs to update, im always open for any advice. But for now i have played around with codes to edit and update the firearms table and the SQL worked great. If anything should change like the firearm gets destroyed or is beyond repairs then its the duty of the DFO to edit and assign a different firearm to an officer and indicate the status of the firearm.

The DB is going to be completely blank. Each officer must register himself or done so by the designated firearms officer. What will happen is that the DFO has a username he or she uses to log into the DB (they can r. From there they can print or email reports. When the officers come to the safe to bookout a firearm, they will be presented with the preBooking form to validate competency and it redirects that officer to the next form which is the book out form. Also each officer has a firearm permit that they keep with them and on that permit is the firearm assigned to them. So whichever way you see it, each officer registered must be assigned with a firearm. If management (who is not being assigned a firearm) wants to use the DB they will have their own username to use the DB as they wish.

Go to the top of the page
 
projecttoday
post Sep 13 2019, 05:13 AM
Post#19


UtterAccess VIP
Posts: 11,067
Joined: 10-February 04
From: South Charleston, WV


DoCmd.RunSQL and CurrentDb.Execute do the same thing. So you are updating the table twice. So the result is correct. But there is no point in doing it twice.
QUOTE
strSQL = "UPDATE tblFirearms SET Assigned = '1' WHERE FirearmSerial = " & """" & Me.cboFirearmSerial.Value & """"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
CurrentDb.Execute strSQL, dbFailOnError

--------------------
Robert Crouser
Go to the top of the page
 
BruceM
post Sep 13 2019, 06:39 AM
Post#20


UtterAccess VIP
Posts: 7,968
Joined: 24-May 10
From: Downeast Maine


To add a little to that point, I would use Execute. If you set warnings off it is best to set them back on in the form's error handler, to be sure they are turned back on no matter what else happens:
CODE
Private Sub cmdSave_Click()

On Error Go To ProcErr:

' Code here

ProcExit:
  DoCmd.SetWarnings True
  Exit Sub

ProcErr:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in " _
               "cmdSave_Click, FormName"
  Resume ProcExit

End Sub

Whether or not there is an error, SetWarnings is set to True. Again, I would use Execute and not bother with the warnings, but the point is worth mentioning.

Also, note my previous comment about the value of a True/False field, if that is what Assigned is. Best is to use the type of value the field is expecting.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 03:44 AM