Automate form aspect

[tobyccdata]

Hi everyone, I need help with a project I am working on for a new job. Only been using Access for two days.
I have been asked to create a database where we can input payment processing.
One requirement is that when we input a transaction that the bank batch field populates.
The bank batch field is in a separate table and is an auto generated number. However, my problem is that in any one day we need to use three different batch numbers, so for instance the table will be –
Batch Number Type
348 Cash
349 Credit Card
350 Cheque
351 Cash
352 Cheque
353 Cheque
Now I have a form that creates the batch number when needed.
When my problem lies here:
The user opens a form to input the financial data. From a drop down menu they select the payment type, from this I want the batch number to automatically come up with the last record in the table that matches.
So they may select ‘Cheque’ and I want Batch Number = 353
Please advise on how this can be accomplished.
I don’t have knowledge on writing code but I am starting to learn.
Thank you Toby.

 

[arnelgp]

you add code to your combobox's After Update event:

Private Sub yourComboName_AfterUpdate()
	Dim var As Variant
	'change the FieldName, combobox name, Table name to what you have.
	var = DMax("[Batch Number]", "BatchNumberTable", "[Type]='" & Me.yourCombName & "'")
	If Not IsNull(var) Then
		'If you want to show in a textbox
		Me.txtBatch = var
		'or in Label
		'Me.lblBatch.Caption=var
	End If
End Sub

 

[bob fitz]

Cross posted: http://www.accessforums.net/showthread.php?t=67640

Please read: https://www.excelguru.ca/content.php?184

 

[tobyccdata]

Sorry for the late reply and the double post I have sorted and understand.

I Have applied your code and mine looks like this

Private Sub Combo174_AfterUpdate()
Dim var As Variant
'change the FieldName, combobox name, Table name to what you have.
var = DMax("[Batch Number]", "Batching", "[Type]='" & Me.Combo174 & "'")
If Not IsNull(var) Then
'If you want to show in a textbox
Me.Bank_Batch = var
'or in Label
'Me.lblBatch.Caption=var
End If
End Sub

It works kind of the only problem I have is when i select say Cash from the combo box drop down menu it just brings the last batch number not the one that aligns with Cash. But it works with cheque and credit card so my thinking is I have managed to misspell something along the line, I will check.

A follow up question please, I now have been asked to make separate input screens for Credit cards, so in credit cards I have the batch number field, my thinking was in the field which is Bank_Batch in the after event space i would put:

=Dmax("[Batch Number]", "Batching", "[Type]=Credit Card")

This would then have the bank batch number prefilled for new records. However it is not working could you please advise me on what is wrong with my input.

Thanks for helping a novice

 

[arnelgp]

I would say almost.
your code only retrieve the last batch number.
you have to add 1 to the last batch number.

var = Nz(DMax("[Batch Number]", "Batching", "[Type]='" & Me.Combo174 & "'"),0) + 1

 

[tobyccdata]

Thanks for the reply

=Dmax("[Batch Number]", "Batching", "[Type]=Credit Card")

For this code the credit card may not be the last batch number. Say there is currently 20 batch numbers. The last credit card batch number is 17.

So i want this field to show batch 17, and then when a new credit card batch number shows up say batch 22 = credit card i wont it to take Batch 22.

Thats what i am confused about how to match the description with the code thats why i tried -

=Dmax("[Batch Number]", "Batching", "[Type]=Credit Card")

But it doesnt work

 

[arnelgp]

you have typo error there:

"[Type]=Credit Card'"

should be:

"[Type]='Credit Card'"

i think i know what you mean, or do i?
if i am getting what you need you only need the last Batch Number + 1.
so you don't need to include the Filter expression on your Dmax function:

=Nz(Dmax("[Batch Number]","Batching"), 0) + 1

this will give you the last batch number (in your table) plus 1 (for new record you are working).

 

[arnelgp]

or simply:

=Nz(DCount("*","Batching"), 0) + 1

 

[tobyccdata]

Hi thanks I will test it out as soon as i get a minute.

The final details of these formula's are still pickling me.

I think i do need to include the filter expression because what happens at the start of the day we need to create three different unique batch numbers one for credit card, one for cheque and one for cash.

When inputting data there are two screens one for cheque and cash and one for credit cards.

For cheque and cash i have a drop down menu, to select which one, so if cash is selected is comes up with the last batch number that corresponds to cash.

And in the credit card screen when we add a new record it automatically picks the last match that corresponds to credit card.

 

[moke123]

what happens at the start of the day we need to create three different unique batch numbers one for credit card, one for cheque and one for cash.

Why not just add a date field to your batch table and use that as your criteria?

 

[static]

For cheque and cash i have a drop down menu, to select which one.

You can display the description and highest batch in the same list if you add another column. With the Batch column as the Bound column the list displays the description while providing the batch ID.

edit: Combo rowsource =

select top 1 batch, numbertype from Batching where numbertype='cash' order by batch desc
union
select top 1 batch, numbertype from Batching where numbertype='credit card' order by batch desc
union
select top 1 batch, numbertype from Batching where numbertype='cheque' order by batch desc

edit: Also, if you added the above combo to a new form along with a subform, you could also load the correct form into the subform when the list changes (based on Description), so the user doesn't need to swap between screens or wonder if they are in the correct one, and you use the combo as the master/child link.

 

[arnelgp]

i'm sorry i misread your 1st post.
so the batch number are auto-generated or already was generated.

tell me to which table does your financial data saved.
what table and fieldname the autogenerated batch number is being save.

ok, ill just give you an example and complete it yourself.

say your financial data table is: t1
and the field to where the batch numbers being saved is on field t1batchNo.
to get the Next batch number from your auto generated batch number table:


add this to Standard Module:

Public Function fnNextBatch(strTable As String, strType As String) As Long
	Dim db As Dao.Database
	Dim rs As Dao.Recordset
	Dim lngRet As Long

	' strTable = your financial data table (if you have different table for each Type)
	' t1batchNo = field where batch number is being saved in strTable

On Error Goto Err_Handler:

	Set db = CurrentDB
	'if t1 table has Type field:
	Set rs = db.OpenRecordSet("Select Top 1 t1batchNo From t1 Where [Type]='" & strType "'", dbOpenSnapshot)

	'if t1 table has No Type field:
	'Set rs = db.OpenRecordSet("Select Top 1 t1batchNo From t1", dbOpenSnapshot)

	If (rs.BOF And rs.EOF) Then
		' no record was returned
		' probably table has no record so we get the Min Batch from table Batching
		lngRet = DMin("Batch Number", "Batching", "Type='" & strType & "'")
	Else
		' lngRet holds the last batch number from financial data table
		rs.MoveFirst
		lngRet = rs(0).Value
		
		' now we need to get the next series of this Type of batch number
		' close rs and use Batching rs.
		rs.Close
		set rs = db.OpenRecordset("Select Top 1 [Batch Number] From Batching " & _
			"Where [Type]='" & strType & "' And [Batch Number]>" & lngReg & " " & _
			"Order By [Batch Number] ASC;")
		lngRet = rs(0).Value

	End If
	rs.Close			
Exit_Function:
	If Not (rs Is Nothing) Then Set rs = Nothing
	If Not (db Is Nothing) Then Set db = Nothing
	Exit Function
Err_Handler:
	Msgbox Err.Number & ": " & Err.Description
	Resume Exit_Function
End Function

your textbox Control Source:

'replace t1 with the correct table name for your transaction table (finacial data table, etc)
=fnNextBatch("t1", "Credit Card")

i hope it is clear.

 

[tobyccdata]

Don't be sorry arnelgp, you are being a great help.

Exactly the batch number is already generated.

the financials are stored in table 'trans_data'
the batch number is stored in table 'batchnum'
the table has the field for batch number called 'batch number' and field 'description' which contains the type ie. credit card.


I will study your code and try to understand. Thank you.

 

[moke123]

I think we, at least I, need more info as to your structure.

at the start of the day we need to create three different unique batch numbers one for credit card, one for cheque and one for cash

so would that look like:

ID     Description     BatchNum
1          Cash           1
2          Check          2
3          Credit         3

or

Id     cash     check     credit
1       1         2          3

If the latter I would add a date field

Id     BatchDate     Cash  Check   Credit
1        8/25/17       1     2       3

then your dlookups can be by date

=DLookUp("Cash","tblBatchNums","BatchDate = #" & Date() & "#")

 

[arnelgp]

mr.moke123, what you are saying is for only 1 transaction.
there are numerous trasactions for cash, credit card, etc.
for one day.
there is already pre-prepared batch numbers.
we don't know how many. for worse case, say a thousand.
batch numbers are sequencial but can hold any Type.

there is also a an existing transaction table that is using these numbers.
all we have to do is get the latest batch that was used from the
transaction table, for a specic Type, then get the Next higher number from the
pre-prepared batch.

 

[moke123]

arnel,
thats my question.

my problem is that in any one day we need to use three different batch numbers

I dont see where the OP states that they need to increase +1 for each transaction.

 

[tobyccdata]

Thanks moke123, so this is an example of what I am trying to achieve, i should of done this earlier, my mistake.

 

[moke123]

well the simple answer then is

=DMax("[Batch Number]","tblBatchNums","Type ='Cash'")

It would be much clearer if you included a few records in your transaction table. Does a batch number represent anything else other than what type of payment it is? How many transactions will have the same batch number? what is it that distinguishes 2 different batch numbers for cash on the same day?

I think I'm confused due to the issue of using a Batch number +1 as opposed to a batch number representing a collection of similar items.

 

[arnelgp]

try this example.
you can't use trans_date there as a basis
of your batch num.

supposed in a day there are 100 batch nums for cash
and you put date there on batch 1-aug-2017.

you only consume, say 12 batch for trans 1-aug-2017.
what will happen to the rest of batch 13-100.
they will be un-used (orphaned).
therefore, when the time to audit, you will need
to explain why you have broken batch nos. in transaction.

i don't know what business your into, but if i
were an auditor, i will have a doubt to what
happens there. maybe will lead to an investigation.

of course this is but a story.

i also added to transaction table [Type] field.
so, you only have one table for all types of
transaction.