Addnew method/Dynamic Array

[Guest]

I’m working on the main form for a database that assigns users batch numbers
depending on the type of function needed. I created a function that returns
a variable length array (containing batch numbers) to the subroutine seen
below. I'm trying to add new records to the assignedbatches table for each
batch number that is in the array, but my code is not working. What am I
doing wrong?

TIA!



Private Sub AssignBatch(strBatchNumbers() As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Set db = CurrentDb

'Open recordset
Set rs = db.OpenRecordset("AssignedBatches")

For i = 1 To UBound(strBatchNumbers)

With rs

rs.AddNew
!BatchNum = strBatchNumbers(i)
!EmpNum = cboUserId.Column(0)
!StartDate = Me!txtStartDate
!Notes = Me!txtNotes
If optTranType = 1 Then !TypeNum = "19" ‘hardcoded for now, will fix later
If optTranType = 2 Then !TypeNum = "20"
If optTranType = 3 Then !TypeNum = Me!cboNonOverride.Column(2)
.Update
End With

Next i
rs.Close
End Sub

 

[Dirk Goldgar]

I'm working on the main form for a database that assigns users batch
numbers depending on the type of function needed. I created a
function that returns a variable length array (containing batch
numbers) to the subroutine seen below. I'm trying to add new records
to the assignedbatches table for each batch number that is in the
array, but my code is not working. What am I doing wrong?

TIA!



Private Sub AssignBatch(strBatchNumbers() As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Set db = CurrentDb

'Open recordset
Set rs = db.OpenRecordset("AssignedBatches")

For i = 1 To UBound(strBatchNumbers)

With rs

rs.AddNew
!BatchNum = strBatchNumbers(i)
!EmpNum = cboUserId.Column(0)
!StartDate = Me!txtStartDate
!Notes = Me!txtNotes
If optTranType = 1 Then !TypeNum = "19" 'hardcoded for now, will
fix later If optTranType = 2 Then !TypeNum = "20"
If optTranType = 3 Then !TypeNum = Me!cboNonOverride.Column(2)
.Update
End With

Next i
rs.Close
End Sub

You don't say in what way the code is "not working". That information
would help a lot.

I see one possible problem, though it may not be a problem in this case,
depending on how you load the array that you pass to the sub. You have
this looping statement:

For i = 1 To UBound(strBatchNumbers)

But most arrays in VBA have 0 as their lowest subscript by default. You
would do better to insulate yourself against variations in array bounds
by writing:

For i = LBound(strBatchNumbers) To UBound(strBatchNumbers)

Aside from that, I see some minor inefficiencies in your code, but
nothing that would make it "not work". So you'd better describe the
symptoms.

 

[Guest]

Oh Dirk, Thank you SO much for your response. You assured me that I am on
the right track, so I continued troubleshooting rather than going about it
some other way and tumbling down another hole somewhere. I figured out why
it wasn't working. I had my function retrieving the actual batch number
instead of the primary key. Referential integrity was not allowing me to add
to a foreign key field in the assignedbatches table because it did exist.
Simple mistake, eh?

I am so excited it is working! I am redesigning this db for my job.
Currently they have just all the data in one table. I was able to normalize
it into 7 tables! This is my first attempt at programming an Access database
although I've created simple dbs from scratch before. I'm an aspiring
programmer, working in my first IT related position, so this is a huge deal
for me. I've been dreaming about this every night and thinking about it
nearly every waking moment as well!

Not to get my head too far into the clouds, I would really appreciate it if
you could expand upon the minor inefficiencies that you have spoken of.
Could it be that I don't need to open a rs if all I am doing is adding
records? (I read that online recently...) As an aspiring programmer, I
certainly care more than just about my program working, but that it is
efficient as well.

Again, thank you so much! I feel very blessed that you helped me out.

Regards,

~AA

 

[Dirk Goldgar]

Oh Dirk, Thank you SO much for your response. You assured me that I
am on the right track, so I continued troubleshooting rather than
going about it some other way and tumbling down another hole
somewhere. I figured out why it wasn't working. I had my function
retrieving the actual batch number instead of the primary key.
Referential integrity was not allowing me to add to a foreign key
field in the assignedbatches table because it did exist. Simple
mistake, eh?

That's such a common phenomenon -- thinking you've done something
technically wrong, when really it's just a logic error. I think we've
all experienced that.

I am so excited it is working! I am redesigning this db for my job.
Currently they have just all the data in one table. I was able to
normalize it into 7 tables! This is my first attempt at programming
an Access database although I've created simple dbs from scratch
before. I'm an aspiring programmer, working in my first IT related
position, so this is a huge deal for me.

Well done, and congratulations.

I've been dreaming about
this every night and thinking about it nearly every waking moment as
well!

We've hooked another one!

Not to get my head too far into the clouds, I would really appreciate
it if you could expand upon the minor inefficiencies that you have
spoken of. Could it be that I don't need to open a rs if all I am
doing is adding records? (I read that online recently...) As an
aspiring programmer, I certainly care more than just about my program
working, but that it is efficient as well.

You *could* do it by executing a series of in-line append queries, and
that *might* be more efficient -- I'd have to benchmark it to be sure.
If you were just adding one record, I would do that instead of using a
recordset. However, since you're looping through an array to add
multiple records, I think the logic and simplicity of using a recordset
as you're doing offsets any minor, theoretical efficiency gain there.

I would suggest the following modifications to what you've got:

[1]

Set rs = db.OpenRecordset("AssignedBatches")

No need to bring any existing records into the recordset, since you're
only going to be adding new ones. Try changing the above to:

Set rs = db.OpenRecordset( _
"AssignedBatches", _
dbOpenDynaset, dbAppendOnly)

Or you could just write this:

Set rs = db.OpenRecordset( _
"SELECT * FROM AssignedBatches WHERE False")

Either way, you wouldn't be loading any of the existing records.

[2]

rs.AddNew

I think this is jus an oversight on your part, but since you're inside a
"With rs" block, you don't need the "rs." qualifier. Change to:

.AddNew

[3]

If optTranType = 1 Then !TypeNum = "19"
If optTranType = 2 Then !TypeNum = "20"
If optTranType = 3 Then !TypeNum = Me!cboNonOverride.Column(2)

These mutually exclusive options would be better expressed in a Select
Case structure:

Select Case Me!optTranType
Case 1
!TypeNum = "19"
Case 2
!TypeNum = "20"
Case 3
!TypeNum = Me!cboNonOverride.Column(2)
End Select

Since the cases are so short and simple, you could make that a bit
tighter like this:

Select Case Me!optTranType
Case 1: !TypeNum = "19"
Case 2: !TypeNum = "20"
Case 3: !TypeNum = Me!cboNonOverride.Column(2)
End Select

That's no more efficient, but a little easier on the eyes.

Note that the above code implies that TypeNum is a text field. If it's
a number field, you're doing unnecessary string-to-number conversions
with the literals "19" and "20".

[4]
No big deal, but I would consider it a better and safer practice always
to qualify the control references with "Me". You mostly did that, but
this line:

!EmpNum = cboUserId.Column(0)

should be

!EmpNum = Me!cboUserId.Column(0)

In this case, failing to qualify it isn't going to cost you anything,
but it's a good habit to get into, since there could be circumstances
where Access/VBA doesn't resolve a reference the way you would think;
for example, if you had a variable and a control with the same name.

It looks like you're doing a great job, AA. Keep up the good work!

 

[Guest]

Hi Dirk,

Thank you for your helpful suggestions. I cleaned up several things that
you pointed out. I have a question about one thing you said:

Select Case Me!optTranType

Case 1: !TypeNum = "19"
Case 2: !TypeNum = "20"
Case 3: !TypeNum = Me!cboNonOverride.Column(2)
End Select

That's no more efficient, but a little easier on the eyes.

Note that the above code implies that TypeNum is a text field. If it's
a number field, you're doing unnecessary string-to-number conversions
with the literals "19" and "20".

The TypeNum field is actually a number field. How do I code this so there
is not a conversion? I've tried a few things, but haven't figured it out.

I have another problem I was hoping you could help me with as well. Now
that I can successfully add records to the AssignedBatches table, the next
step is to show the user the added records. What I am trying to do is query
the function that contains the current batch numbers. The batch numbers are
stored in a Public Function called GetBatchNumbers() within the current form.
I created a query detailing all the fields the user needs to see with the
criteria: Where MasterBatch.BatchNum = GetBatchNumbers()

I am opening the query using:

docmd.OpenQuery "qryAssignedBatches", acviewnormal, acReadonly

I am getting a runtime error (3085): undefined function 'GetBatchNumbers' in
expression.

I am pretty certain it's not the SQL syntax that's causing the error. Is my
problem that I am opening the query from within a subroutine & it can't see
the function data? Or do I need to have the function in it's own module for
this work?

If i need the function in a module, I was thinking maybe it'd be easier to
do a temporary table instead.

I could use some help!
Thanks,
AA

Oh Dirk, Thank you SO much for your response. You assured me that I
am on the right track, so I continued troubleshooting rather than
going about it some other way and tumbling down another hole
somewhere. I figured out why it wasn't working. I had my function
retrieving the actual batch number instead of the primary key.
Referential integrity was not allowing me to add to a foreign key
field in the assignedbatches table because it did exist. Simple
mistake, eh?

That's such a common phenomenon -- thinking you've done something
technically wrong, when really it's just a logic error. I think we've
all experienced that.

I am so excited it is working! I am redesigning this db for my job.
Currently they have just all the data in one table. I was able to
normalize it into 7 tables! This is my first attempt at programming
an Access database although I've created simple dbs from scratch
before. I'm an aspiring programmer, working in my first IT related
position, so this is a huge deal for me.

Well done, and congratulations.

I've been dreaming about
this every night and thinking about it nearly every waking moment as
well!

We've hooked another one!

Not to get my head too far into the clouds, I would really appreciate
it if you could expand upon the minor inefficiencies that you have
spoken of. Could it be that I don't need to open a rs if all I am
doing is adding records? (I read that online recently...) As an
aspiring programmer, I certainly care more than just about my program
working, but that it is efficient as well.

You *could* do it by executing a series of in-line append queries, and
that *might* be more efficient -- I'd have to benchmark it to be sure.
If you were just adding one record, I would do that instead of using a
recordset. However, since you're looping through an array to add
multiple records, I think the logic and simplicity of using a recordset
as you're doing offsets any minor, theoretical efficiency gain there.

I would suggest the following modifications to what you've got:

[1]

Set rs = db.OpenRecordset("AssignedBatches")

No need to bring any existing records into the recordset, since you're
only going to be adding new ones. Try changing the above to:

Set rs = db.OpenRecordset( _
"AssignedBatches", _
dbOpenDynaset, dbAppendOnly)

Or you could just write this:

Set rs = db.OpenRecordset( _
"SELECT * FROM AssignedBatches WHERE False")

Either way, you wouldn't be loading any of the existing records.

[2]

rs.AddNew

I think this is jus an oversight on your part, but since you're inside a
"With rs" block, you don't need the "rs." qualifier. Change to:

.AddNew

[3]

If optTranType = 1 Then !TypeNum = "19"
If optTranType = 2 Then !TypeNum = "20"
If optTranType = 3 Then !TypeNum = Me!cboNonOverride.Column(2)

These mutually exclusive options would be better expressed in a Select
Case structure:

Select Case Me!optTranType
Case 1
!TypeNum = "19"
Case 2
!TypeNum = "20"
Case 3
!TypeNum = Me!cboNonOverride.Column(2)
End Select

Since the cases are so short and simple, you could make that a bit
tighter like this:

Select Case Me!optTranType
Case 1: !TypeNum = "19"
Case 2: !TypeNum = "20"
Case 3: !TypeNum = Me!cboNonOverride.Column(2)
End Select

That's no more efficient, but a little easier on the eyes.

Note that the above code implies that TypeNum is a text field. If it's
a number field, you're doing unnecessary string-to-number conversions
with the literals "19" and "20".

[4]
No big deal, but I would consider it a better and safer practice always
to qualify the control references with "Me". You mostly did that, but
this line:

!EmpNum = cboUserId.Column(0)

should be

!EmpNum = Me!cboUserId.Column(0)

In this case, failing to qualify it isn't going to cost you anything,
but it's a good habit to get into, since there could be circumstances
where Access/VBA doesn't resolve a reference the way you would think;
for example, if you had a variable and a control with the same name.

It looks like you're doing a great job, AA. Keep up the good work!

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Hi Dirk,

Thank you for your helpful suggestions.

You're welcome.

I cleaned up several things
that you pointed out. I have a question about one thing you said:

Select Case Me!optTranType

The TypeNum field is actually a number field. How do I code this so
there is not a conversion? I've tried a few things, but haven't
figured it out.

The literals are easy. The combo box's column is always going to have
to be converted, so you probably don't have to concern yourself with it;
you could change the code to read as follows:

Select Case Me!optTranType
Case 1: !TypeNum = 19
Case 2: !TypeNum = 20
Case 3: !TypeNum = Me!cboNonOverride.Column(2)
End Select

If you want, you can force an immediate conversion to the data type of
TypeNum. For example, if TypeNum is a Long Integer, you could write
this:

Select Case Me!optTranType
Case 1: !TypeNum = 19&
Case 2: !TypeNum = 20&
Case 3: !TypeNum = CLng(Me!cboNonOverride.Column(2))
End Select

Now, *that's* getting really finicky. The '&' suffix on the numeric
literals specifies that these literals represent Long Integer values,
not just Integers. I wouldn't normally bother with that, except maybe
in code that will run many times in a tight loop, where the utmost
efficiency is required. The use of the CLng() function ensures that the
combo column is converted directly to a Long Integer value, just in case
the compiler might take it into its head to use some intermediate type.
I don't know that the compiler would do that, so as I said, this is very
finicky indeed.

For most purposes, I'd just use the first of the two versions I posted
above. After all, what you gain in execution efficiency now, you might
lose later if you changed the data type of TypeNum from Long Integer to
Integer, or some other numeric type.

I have another problem I was hoping you could help me with as well.
Now that I can successfully add records to the AssignedBatches table,
the next step is to show the user the added records. What I am
trying to do is query the function that contains the current batch
numbers. The batch numbers are stored in a Public Function called
GetBatchNumbers() within the current form. I created a query
detailing all the fields the user needs to see with the criteria:
Where MasterBatch.BatchNum = GetBatchNumbers()

That's not going to work, even if you add the required reference to the
form itself, to qualify the function call:

WHERE MasterBatch.BatchNum =
Forms!YourForm.GetBatchNumbers()

The above is the way you have to refer to a public function defined in a
form, from anyplace not on that form itself (as your query is not).
HOWEVER, the above still won't work! And it wouldn't work even if you
moved the function to a standard module and dropped the "Forms!FormName"
qualifier.

I'm not sure what you mean when you say the batch numbers are "stored in
a Public Function", but the function can only return one thing. That
thing may be a string containing a list of batch numbers, or it may be
an array of them, or it may be a collection of them, but as far as the
SQL expression is concerened, your WHERE clause is comparing one
"thing" -- MasterBatch.BatchNum -- to another "thing" -- the return
value of GetBatchNumbers(). The two things have to be comparable, and
if one is a single number and the other is a string, array, or
collection of numbers, they just aren't comparable.

What you want, as I understand it, is to set up a WHERE criterion that
evaluates to True of MasterBatch.BatchNum is *in* the
list/array/collection. There are several ways you could go about doing
that, including using an alternate version of the function that receives
a batch number as an argument and returns True or False depending on
whether the batch number is in the list. However, that may not be the
most efficient way of doing it. It will depend on what you mean when
you say, "The batch numbers are stored in a Public Function". If you'd
like to post the code for the function GetBatchNumbers(), I'll try to
make a better suggestion.

 

[Guest]

Thanks for your reply. I apologize for not being clear, the function returns
an array of batch numbers. Despite my lack of clarity, it sounds like you
understand what I am trying to accomplish. : )

Here is a snippet of the function code that returns an array of batch
numbers. There are actually 16 case statements. (fyi: The ones not listed
are in the nested case statement for cbo!nonoverride)

A brief overview - the form contains an option group where if the first two
options are selected, I know what the batch number should start with. Option
1 is override , so the batch number needs to start with a 4 or a 5. Option 2
is Override Payoff and the batch number needs to start with an 8. If Option
3 is selected, which is Non-Override, I can't tell alone by that selection
what the batch number needs to start with. Instead, the combo box
cboNonOverride needs to be evaluated to determine what the batch number
should start with. (This combo box is hidden on the form unless Opt 3 is
selected and then it becomes visible)

For easy reference, I have also included the AssignBatch procedure as well.
Based on your advice thus far, I was thinking that maybe I could merge the
two procedures into one function and change it to return a Boolean type. and
then create a subroutine that displays the updated records. Or I could leave
it as is, and simply query for the employee & the start date chosen. This is
not ideal of course because I really just want the user to see only the
records assigned for a particular request.

On an aside, I am aware that the function needs error handling. It's on my
to do list. One known issue is that if there is a shortage of batch numbers
to assign, the function will the array, but will include duplicate batch
numbers. Although it's unlikely we'd ever run out batch numbers, I wouldn't
dream of not fixing this.

Private Function GetBatchNumbers()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim intCount As Integer
Dim intCounter As Integer
Dim rs As DAO.Recordset
Dim bmkReturnHere As Variant
Dim strBatchNumbers() As String


strSQL = "SELECT MasterBatch.BatchNumber, MasterBatch.BatchNum FROM
MasterBatch LEFT JOIN AssignedBatches ON MasterBatch.BatchNum =
AssignedBatches.BatchNum WHERE AssignedBatches.BatchNum Is Null"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
bmkReturnHere = rs.Bookmark


intCount = Me!txtQuantity

Select Case Me!optTranType

Case 1:

'Override Batch Type has been selected

ReDim strBatchNumbers(1 To intCount)
bmkReturnHere = rs.Bookmark
rs.FindFirst "Left$(BatchNumber,1) = '4' or Left$(BatchNumber,1) = '5'"
For intCounter = 1 To intCount
strBatchNumbers(intCounter) = rs!BatchNum
rs.FindNext "Left$(BatchNumber,1) = '4' or Left$(BatchNumber,1) = '5'"
Next intCounter
AssignBatch strBatchNumbers()

Case 2:

'Override-Payoff Has been selected

ReDim strBatchNumbers(1 To intCount)
bmkReturnHere = rs.Bookmark
rs.FindFirst "Left$(BatchNumber,1) = '8'"
For intCounter = 1 To intCount
strBatchNumbers(intCounter) = rs!BatchNum
rs.FindNext "Left$(BatchNumber,1) = '8'"
Next intCounter
AssignBatch strBatchNumbers()

Case 3:

' Non-Override Batch Type has been selected/Evaluate non-override type

Select Case Me!cboNonOverride

Case "Analysis"
ReDim strBatchNumbers(1 To intCount)
bmkReturnHere = rs.Bookmark
rs.FindFirst "Left$(BatchNumber,1) = 'A'"
For intCounter = 1 To intCount
strBatchNumbers(intCounter) = rs!BatchNum
rs.FindNext "Left$(BatchNumber,1) = 'A'"
Next intCounter
AssignBatch strBatchNumbers()
End Select

End Select

rs.close
End Function

--------------------------------------
Private Sub AssignBatch(strBatchNumbers() As String)
'Dim i As Integer
'For i = 1 To UBound(strBatchNumbers)
'Debug.Print strBatchNumbers(i)
'Next i
'End Sub

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Set db = CurrentDb

'Open recordset
Set rs = db.OpenRecordset("AssignedBatches", dbOpenDynaset, dbAppendOnly)

'Add records to AssignedBatches table
For i = 1 To UBound(strBatchNumbers)
With rs
.AddNew
!BatchNum = strBatchNumbers(i)
!EmpNum = Me!cboUserId.Column(0)
!StartDate = Me!txtStartDate
!Notes = Me!txtNotes
Select Case Me!optTranType
Case 1: !TypeNum = "24"
Case 2: !TypeNum = "25"
Case 3: !TypeNum = Me!cboNonOverride.Column(1)
End Select
.Update
End With

Next i


rs.Close

End Sub

 

[Dirk Goldgar]

Thanks for your reply. I apologize for not being clear, the function
returns an array of batch numbers. Despite my lack of clarity, it
sounds like you understand what I am trying to accomplish. : )

Here is a snippet of the function code that returns an array of batch
numbers. There are actually 16 case statements. (fyi: The ones not
listed are in the nested case statement for cbo!nonoverride)

A brief overview - the form contains an option group where if the
first two options are selected, I know what the batch number should
start with. Option 1 is override , so the batch number needs to
start with a 4 or a 5. Option 2 is Override Payoff and the batch
number needs to start with an 8. If Option 3 is selected, which is
Non-Override, I can't tell alone by that selection what the batch
number needs to start with. Instead, the combo box cboNonOverride
needs to be evaluated to determine what the batch number should start
with. (This combo box is hidden on the form unless Opt 3 is selected
and then it becomes visible)

For easy reference, I have also included the AssignBatch procedure as
well. Based on your advice thus far, I was thinking that maybe I
could merge the two procedures into one function and change it to
return a Boolean type. and then create a subroutine that displays
the updated records. Or I could leave it as is, and simply query for
the employee & the start date chosen. This is not ideal of course
because I really just want the user to see only the records assigned
for a particular request.

On an aside, I am aware that the function needs error handling. It's
on my to do list. One known issue is that if there is a shortage of
batch numbers to assign, the function will the array, but will
include duplicate batch numbers. Although it's unlikely we'd ever
run out batch numbers, I wouldn't dream of not fixing this.

Private Function GetBatchNumbers()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim intCount As Integer
Dim intCounter As Integer
Dim rs As DAO.Recordset
Dim bmkReturnHere As Variant
Dim strBatchNumbers() As String


strSQL = "SELECT MasterBatch.BatchNumber, MasterBatch.BatchNum FROM
MasterBatch LEFT JOIN AssignedBatches ON MasterBatch.BatchNum =
AssignedBatches.BatchNum WHERE AssignedBatches.BatchNum Is Null"
Set qdf = CurrentDb.CreateQueryDef("", strSQL)
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
bmkReturnHere = rs.Bookmark


intCount = Me!txtQuantity

Select Case Me!optTranType

Case 1:

'Override Batch Type has been selected

ReDim strBatchNumbers(1 To intCount)
bmkReturnHere = rs.Bookmark
rs.FindFirst "Left$(BatchNumber,1) = '4' or Left$(BatchNumber,1)
= '5'" For intCounter = 1 To intCount
strBatchNumbers(intCounter) = rs!BatchNum
rs.FindNext "Left$(BatchNumber,1) = '4' or Left$(BatchNumber,1) =
'5'" Next intCounter
AssignBatch strBatchNumbers()

Case 2:

'Override-Payoff Has been selected

ReDim strBatchNumbers(1 To intCount)
bmkReturnHere = rs.Bookmark
rs.FindFirst "Left$(BatchNumber,1) = '8'"
For intCounter = 1 To intCount
strBatchNumbers(intCounter) = rs!BatchNum
rs.FindNext "Left$(BatchNumber,1) = '8'"
Next intCounter
AssignBatch strBatchNumbers()

Case 3:

' Non-Override Batch Type has been selected/Evaluate non-override
type

Select Case Me!cboNonOverride

Case "Analysis"
ReDim strBatchNumbers(1 To intCount)
bmkReturnHere = rs.Bookmark
rs.FindFirst "Left$(BatchNumber,1) = 'A'"
For intCounter = 1 To intCount
strBatchNumbers(intCounter) = rs!BatchNum
rs.FindNext "Left$(BatchNumber,1) = 'A'"
Next intCounter
AssignBatch strBatchNumbers()
End Select

End Select

rs.close
End Function

--------------------------------------
Private Sub AssignBatch(strBatchNumbers() As String)
'Dim i As Integer
'For i = 1 To UBound(strBatchNumbers)
'Debug.Print strBatchNumbers(i)
'Next i
'End Sub

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Set db = CurrentDb

'Open recordset
Set rs = db.OpenRecordset("AssignedBatches", dbOpenDynaset,
dbAppendOnly)

'Add records to AssignedBatches table
For i = 1 To UBound(strBatchNumbers)
With rs
.AddNew
!BatchNum = strBatchNumbers(i)
!EmpNum = Me!cboUserId.Column(0)
!StartDate = Me!txtStartDate
!Notes = Me!txtNotes
Select Case Me!optTranType
Case 1: !TypeNum = "24"
Case 2: !TypeNum = "25"
Case 3: !TypeNum = Me!cboNonOverride.Column(1)
End Select
.Update
End With

Next i


rs.Close

End Sub

I think the following untested code would be more efficient, and would
allow you to run a query that both assigns the batches and returns the
records that it assigns:

'----- start of code -----
Private Function GetBatchNumbers() As String

' Assigns batch numbers, returns a comma-delimited
' list of the batch numbers assigned. The list will
' have leading and trailing commas; e.g, ",XXX,YYY,ZZZ,".

Dim rs As DAO.Recordset
Dim strBatchNumbers() As String
Dim strSQL As String
Dim strCriteria As String
Dim strBatchNoList As String
Dim intCount As Integer
Dim intCounter As Integer

intCount = Me!txtQuantity

' Define basic SQL statement. We'll add more
' criteria to the WHERE clause before running
' the query.
strSQL = _
"SELECT TOP " & intCount & _
" MasterBatch.BatchNumber, MasterBatch.BatchNum " & _
"FROM MasterBatch LEFT JOIN AssignedBatches " & _
"ON MasterBatch.BatchNum = AssignedBatches.BatchNum " & _
"WHERE (AssignedBatches.BatchNum Is Null) AND "

' Figure out what extra criteria should be applied
' to the basic SQL statement.
Select Case Me!optTranType

Case 1:
'Override Batch Type has been selected
strCriteria = _
"BatchNumber Like '4*' Or BatchNumber Like '5*'"

Case 2:
'Override-Payoff Has been selected
strCriteria = "BatchNumber Like '8*'"

Case 3:
' Non-Override Batch Type has been selected.
' Evaluate non-override type

Select Case Me!cboNonOverride

Case "Analysis"
strCriteria = "BatchNumber Like 'A*'"

' ... other cases ...

End Select

End Select

' Now we have the complete criteria.
' Define an array big enough to hold the number
' of batch numbers we want.

ReDim strBatchNumbers(1 To intCount)

' Open a recordset on the query that will
' return the batch numbers.
Set rs = CurrentDb.OpenRecordset( _
strSQL & strCriteria, _
dbOpenSnapshot)

If rs.EOF Then
' ... handle error: no batch numbers available.
Else
For intCounter = 1 To intCount

If rs.EOF Then
' ... handle error: not enough batch numbers
' available.
Else
' Add this number to the array.
strBatchNumbers(intCounter) = rs!BatchNum

' And add it to the delimited list.
strBatchNoList = strBatchNoList & "," & rs!BatchNum

' Move to the next record.
rs.MoveNext
End If

Next intCounter
End If

rs.Close

AssignBatch strBatchNumbers

' Return the list we built, adding the last comma
' to the end of it.
GetBatchNumbers = strBatchNoList & ","

End Function
'----- end of code -----

This is a bit tricky, but as I said above, I *think* you could execute a
query that both assigns the batch numbers and then selects the records
that were assigned. For example,

SELECT * FROM AssignedBatches
WHERE GetBatchNumbers() Like '*,' & BatchNum & ',*'

I believe that would cause the function to be evaluated once, assigning
the batches and returning a comma-delimited list of the batch numbers,
and then that list would be used by the query engine to select just the
records with BatchNum in that list.

 

[Guest]

Hi Dirk!

Thank you so much for your reply. This has been such a wonderful learning
opportunity for me. It makes me laugh that you are like, the following
untested code....when you make no mistakes at all!

I typed the code in by hand so that I could really understand the process.
It is much more efficient than my function by adding the batch number
criteria to the query before opening the recordset. I also appreciate how
the If statements handle the .eof scenarios. My For Next loop didn't have
error handling for that and I wasn't sure how to modify it either.

As for the comma-delimited list- very, very clever of you. It took me some
time to figure out how to get the query to work using the list but I got it
working! I had to declare the function public, and I ended up moving the
function from the form module to a standard code module. I've never worked
with those before, so it was a learning experience involving a lot of trial
and error. I would say I'm most experienced with sql statements and usually
write them by hand in Access instead of using the visual grid. I found the
query to be utterly fascinating how it accepts the function value as
criteria. How cool!

What more can I say? Dirk, you're a genius!

Thanks for all your help! You rock!!

AA