Adding Multiple Records

[Friday]

Access XP running on MS XP. Currently our office issued case numbers to agents in the field manually. By that I mean the agent call in and requests a case number(s), we issue the number, then note their idcard and station number next to the case number for auditing purposes. I have finally gotten permission to automate this task and put it up on the server so the agents can get their own numbers and quit calling in. I have built a simple form that allows the agent to enter his/her idcard number and station number, then click a button to display their case number. I am using a table with the case numer field set to autonumber. All is well. However; what if the agent needs 10 case numbers? (Believe me, it happens). I can leave this thing alone and make them enter and click ten times, but thought there would be a more elegant way of doing it (please enter the number of case numbers you need). But I've pounded my head agains this thing for 3 days and not come up with anything. I know it is something simple (For x to y) but this is beyond me evidently. Any help pointing me in the right direction is appreciated.

 

[Pat Hartman]

I dont't think assigning groups of case numbers is appropriate. Doesn't the agent need to record some information about the case? If that is so, the only solution is one at a time. There should be a minimum number of required fields to even add a record. Otherwise you run the risk of creating unused case numbers.

 

[Friday]

Pat: I couldn't agree more. However, this is the way it works in this police dept: The agent calls in and reserves his case number. He then writes his report and sends it to another office, where it is entered into the system (mainframe system). We audit the case numbers from here from our trustworthy notepad to make sure the numbers issued eventually end up in the database. If an agent is off his territory for a certain amount of time, he will call in and grab several case numbers to write up incidents that occurred in his absence. We only note his idcard number and the station number, no other info is grabbed at this point, since it is not part of the reporting system. I'm just trying to make life easier for us here. It's very annoying to be right in the middle of something important and have the phone ring and the agent at the other end wants 13 case numbers to cover several incidents that occurred while he was on vacation. If he could get them himself, the phone would ring less often...

 

[Pat Hartman]

Since assigning a case Id seems to be separate from entering the case details, you might want a separate form for just this purpose. Then rather than using a bound form, use the DAO or ADO Addnew method that you run in a code loop. So the form needs a field for the agent to identify himself and another to enter the number of id's being requested. Then your code just appends as many rows as necessary making sure to capture the id's as they are generated. The form also contains 10 unbound fields so you can display the generated numbers. Or more if you want to let them generate more. But, be sure to edit the number requested and don't let it be larger than the number of unbound display fields on the form.

The reason for displaying the id's individually rather than as a range is that in a multi-user environment you run the risk of someone else asking for id's at the same time so there may be gaps in the numbers assigned to each user.

 

[Friday]

I follow you. I'll have to do some manual research to see the largest number ever requested at one time. You answered my next question already, about preventing different users hitting the form at the same time and messing up the case numbers. As always, thanks for the excellent advice.

 

[Friday]

Hi, Pat. I got the base code on adding the case numbers, idcard and station number to the table based on what the agent enters on the form. Here is the code:

Private Sub btnGetCase_Click()
Dim agtId As String
Dim agtStation As String
Dim dbsCases As Database
Dim rstCases As Recordset
Dim rstCount As Integer
Dim x As Variant
Dim txtHowMany As Integer

Set dbsCases = CurrentDb
Set rstCases = dbsCases.OpenRecordset("tblCaseNumbers", dbOpenTable, dbAppendOnly)

txtHowMany = Me.txtQty

For x = 1 To txtHowMany

With rstCases
.AddNew
!fldIdcard = Me.txtidcard
!fldStation = Me.txtstation
!fldDateIssued = Date
.Update
End With

Next x

rstCases.Close
Set dbsCases = Nothing

End Sub

Now, I am stumped on how to display these newly added records to the unbound fields on the form. I only need to display the casenumber, which doesn't show in the above code, as it is set as a key-autonumber field. Any thoughts ?

 

[Friday]

Wait a minute. I think I know how to do this. Don't post an answer. I need to figure this out for myself.

 

[saross]

Type Mismatch Error Message

Hi all.
I've used your code here to create a sub that adds a single record onto a table, using the values in the current form to populate the two fields. However, I get an 'Type Mismatch' error message returned and it highlights the Set Recordset line as the problem. Can anyone help and explain why?

CODE:

Private Sub Command60_Click()

Dim db As Database
Dim rsUsers As Recordset
Dim intResponse As Integer

Set db = CurrentDb()
Set rsUsers = db.OpenRecordset("TblSubscriptionUser", dbOpenTable, dbAppendOnly)

rsUsers.AddNew
rsUsers("SubscriptionID") = Me.txtContactID
rsUsers("ContactID") = Me.SelectedSubscriptionID
rsUsers.Update
rsUsers.Close

intResponse = MsgBox("New User Added", vbOKOnly, "Enter New Subscription User")

End Sub

 

[Friday]

Now I Give Up

Can some one point out to me how to get the contents of this array into a listbox? I have been beating my head on the wall on this. I assume I am supposed to use the rowsource property but all i get is the same value repeated, instead of all the values. Here is the code:
Private Sub btnAssgnCase_Click()
Dim dbsCases As DAO.Database
Dim rstCases As DAO.Recordset
Dim rstCount As Integer
Dim rstQCount As Integer
Dim strQdf As DAO.QueryDef
Dim fld As Field
Dim varData(), ub As Long, i As Long


Set dbsCases = CurrentDb
Set rstCases = dbsCases.OpenRecordset("tblCaseNumbers", dbOpenTable, dbReadOnly)

Set strQdf = dbsCases.QueryDefs("FindCaseNumber")


rstQCount = strQdf.Parameters.Count

strQdf.Parameters("[Enter the ID Card]") = Forms!frmCaseDisplayX!txtidcard
strQdf.Parameters("[Enter the Station]") = Forms!frmCaseDisplayX!txtstation
strQdf.Parameters("[Enter the Date]") = Forms!frmCaseDisplayX!txtdate

Set rstCases = strQdf.OpenRecordset

rstCases.MoveLast

ub = rstCases.RecordCount ' upper bound

ReDim varData(ub, 4) '4 columns
rstCases.MoveFirst ' got to the top

For i = 1 To ub
'MsgBox "Recordset contains " & rstCases(3)
varData(i, 1) = rstCases(0) 'column 1
varData(i, 2) = rstCases(1) 'column 2
varData(i, 3) = rstCases(2) 'column 3
varData(i, 4) = rstCases(3) 'column 4
rstCases.MoveNext
Next i

Me.ListBoxA.RowSource = ???????


rstCases.Close
Set dbsCases = Nothing


End Sub

 

[Friday]

OOOPS !!! Never mind I finally got it.