save button on my data entry form (1 Viewer)

[britesynth]

hello and good day!
Need your thoughts on this...

1 client - many loans

ex.
John Doe

[loanID] 10 [loanNumber] 1 [Loan] 60,000 [renewed] yes
[loanID] 28 [loanNumber] 2 [Loan] 45,000 [renewed] yes
[loanID] 45 [loanNumber] 3 [Loan] 55,000 [renewed] no


if a client wants to renew his loan: [proposedLoan] - [outstandingBalance] = [renewable] 23,000

I have a table called temporaryLoan which I will use to append the new record to my Loans table



in theory [renewable] will become

[loanID] 79 [loanNumber] 4 [Loan] 23,000 [renewed] no

and

[loanID] 45 [loanNumber] 3 [Loan] 55,000 [renewed] yes


My questions:

1. any other way to auto-generate [loanNumber] every time a loan is renewed?
for now in a query I use - NewLoanNumber: [loanNumber] + 1

2. how do I make a save button which will save my temporaryLoan table only when I hit save, having a problem with duplicates when I close my data entry form (by accident, a client can change his mind, etc...)

Access: append 3 rows?
Me: whaaattt????
this will mess up my entire database
[loanID] 79 [loanNumber] 4 [Loan] 23,000 [renewed] no
[loanID] 80 [loanNumber] 4 [Loan] 23,000 [renewed] no
[loanID] 81 [loanNumber] 4 [Loan] 23,000 [renewed] no


3. save and append only when required fields are complete, I also want to delete the record on the temporaryLoan table after an append action is made - be it on after closing the form or onclick of a button
I tried using action queries and of course, I get prompts for my append and delete actions which are not very convenient after hitting a single button

Me: (clicked on my save button / appendQ / deleteQ)
Access: prompt! prompt! prompt! prompt!

4. can I do all of this inside 1 save button? me trying and failing lol

How would you go guys about this? not exactly asking for codes but flow/approach
thank you in advance

-jake

 

[Ranman256]

yes you can edit the SAVE button and add in more code, or queries.

no, there is only 1 autonum. If you want items to number incremental for each individual,(like person A's loans, 1,2,3,etc) then the only option is to code it. Loan#=Loan#+1.

turn off the query prompts at the start and end of code:

docmd.Setwarnings False
docmd.openquery "abc"
docmd.openquery "xyz"
docmd.Setwarnings true

 

[arnelgp]

Use unbound form if you want to use save button or transaction

 

[britesynth]

yes you can edit the SAVE button and add in more code, or queries.

no, there is only 1 autonum. If you want items to number incremental for each individual,(like person A's loans, 1,2,3,etc) then the only option is to code it. Loan#=Loan#+1.

turn off the query prompts at the start and end of code:

docmd.Setwarnings False
docmd.openquery "abc"
docmd.openquery "xyz"
docmd.Setwarnings true

hello, thank you for your response

where do I put these codes? not really familiar with vba yet, using macro for now, the first promp I want gone but the second which says "append 3 rows" I want to somehow customize

thank you again

 

[britesynth]

Use unbound form if you want to use save button or transaction

good day sir arnel!

I'm thinking of going this path

can I set my unbound form texts to tempVars and use the values to populate my fields on the loan table? do you think it will work? or just straight up use append?

thank you for your time =)

 

[arnelgp]

Yes tempvar or use your controls to append or update.

 

[britesynth]

I found a link I don't know if I'm allowed to post it here

I think it's just what I need

but it's in vba

I think I understand some of the parts like giving out messages when a required textbox is not completed and checking for nulls, I have tried doing some in macro but some parts I'm not quite familiar with

Mods can I post the link here? it's from another forum I think

thanks!