Re: Addnew method/Dynamic Array

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

"Dirk Goldgar" wrote:

"LilMorePlease" <LilMorePlease@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:44221289-69D4-4B1C-B90D-5A4438B186E4@xxxxxxxxxxxxx
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.

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

(please reply to the newsgroup)



.

Relevant Pages

  • Re: Addnew method/Dynamic Array
    ... Here is a snippet of the function code that returns an array of batch ... I have also included the AssignBatch procedure as ... Dim qdf As DAO.QueryDef ... Dim intCount As Integer ...
    (microsoft.public.access.modulesdaovba)
  • Re: Addnew method/Dynamic Array
    ... Here is a snippet of the function code that returns an array of batch ... Dim qdf As DAO.QueryDef ... Dim intCount As Integer ...
    (microsoft.public.access.modulesdaovba)
  • Re: Automating search for words in a website using WSH
    ... Dim oIE 'Internet Explorer Object ... ' Document Object Model of the loaded web page. ... I will need to get the value of intCount: ... set objWshShell = wscript.createobject ...
    (microsoft.public.scripting.wsh)
  • Re: Automating search for words in a website using WSH
    ... Dim oIE 'Internet Explorer Object ... ' Document Object Model of the loaded web page. ... dim strTo, strCc, strText, strDate, strIncrease, strMessage, intCount, ... set objWshShell = wscript.createobject ...
    (microsoft.public.scripting.wsh)
  • Re: Report has 126 pages and take forever to run
    ... The query runs in 1 second, but the report still takes 25 seconds. ... Dim intCount As Integer ...
    (microsoft.public.access.reports)