how to refresh my form after insert query. (1 Viewer)

[Gr3g0ry]

on my form i need a new id.

the new id is set in a textbox using =MaxPID() set in the textbox's Default Value.

MaxPID() executes a query the last Id from my table + 1. i use this for my new id.

After i execute my insert query on this form, how can i refresh my form and set my new id ( the most recent id+1 ) ?

 

[arnelgp]

i am guess the form is Unbound
since you have an Insert Query there.

after you executed your Insert query,
and before the "End Sub", add this line:

Me.yourTextBoxPID = Eval(Replace(Me.yourTextBoxPID.DefaultValue, "=",""))


you should also reset all other textbox to null
in preparation for new Addition of record:

Private Sub Save_Click()
CurrentDb.Execute "your insert query here..."
' clear all textbox
Me.yourTextbox1 = Null
Me.yourTextbox2 = Null
...
...
Me.yourTextBoxPID = Eval(Replace(Me.yourTextBoxPID.DefaultValue, "=",""))
End Sub

 

[Gr3g0ry]

ok so i tried. and i got this error:
Run-time error '2424'
the expression you entered has a function name that Microsoft Access cant find.

and the debugger points to : Me.PartnerId = Eval(Replace(Me.PartnerId.DefaultValue, "=", ""))

 

[Gr3g0ry]

Here is a snippet of my code:

TRN = Me.cboTrn.Value
StartDate = Me.StartDate.Value
EndDate = DateAdd("m", 6, Me.StartDate.Value)
MaturityDate = Me.MaturityDate.Value
PartnerId = Me.PartnerId.Value
StartAmount = Me.StartAmount.Value
ActualBalance = Me.ActualBalance.Value
MaturitytBalance = Me.MaturityAmount.Value
Installments = Me.Installments.Value
PaymentMethod = Me.cboTerm.Value

DoCmd.RunSQL "INSERT INTO PARTNER (TRN,StartDate,EndDate,StartAmount,MaturityDate,ActualBalance,MaturityBalance,PaymentMethod,PartnerId,Installments) VALUES ('" & TRN & "', #" & StartDate & "#, #" & EndDate & "#, " & StartAmount & ", #" & MaturityDate & "#, " & ActualBalance & ", " & MaturityBalance & ", '" & PaymentMethod & "', " & PartnerId & ", " & Installments & ");"
DoCmd.RunSQL "Insert into PAYMENTS (PartnerId,Amount,PaymentDate) VALUES ('" & PartnerId & "', " & StartAmount & ",#" & StartDate & "#);"

Me.cboTerm.Value = Null
Me.cboTrn.Value = Null
Me.ActualBalance.Value = Null
Me.StartAmount.Value = Null
Me.StartDate.Value = Null
Me.MaturityDate.Value = Null
Me.MaturityAmount.Value = Null
Me.Installments.Value = Null

'Me.Requery


Me.PartnerId = Eval(Replace(Me.PartnerId.DefaultValue, "=", ""))
End Sub

 

[arnelgp]

move your Function, MaxPID(), to
Standard Module (VBE->Insert->Module)

 

[Gr3g0ry]

function:
Public Function MaxPID()
MaxPID = CurrentDb.OpenRecordset("qryMaxPatnerId").Fields(0)
End Function

query:
SELECT Max([PartnerId]+1) AS NextID FROM PARTNER;

so i moved my function to modules as you suggested and named my module MaxPID.

on my form in my textbox's default value i have =MaxPID()

now when i load my form i have #Name? in my textbox.
im using Access 2010 btw

 

[arnelgp]

can you use dlookup instead and not the query:

Public Function MaxPID() As Long
MaxPID = Nz(DMax("PartnerID", "Partner"), 0) + 1
End Function

 

[Gr3g0ry]

nope, didnt work bruh.

i copied ur code and replaced the contents of the module MaxPID, saved and retried

 

[arnelgp]

Dont't name your Module as with the same
name as the function. that will not work.
Remove the module, then insert new module
paste the code there and named it
differently, eg: modMaxPID or moduleMaxPID

 

[Gr3g0ry]

sorry for being such a pain. so ive done what u said.

how do i use that module now ? ive named it ModMaxPID.
remember i have a textbox and ive previously set its default value to my function.

 

[arnelgp]

did you put the code in the ModMaxPID?

Public Function MaxPID() As Long
MaxPID = Nz(DMax("PartnerID", "Partner"), 0) + 1
End Function


if you did, test your form.

 

[Gr3g0ry]

Yes i did. in my textbox i now have #Name?.

usually onLoad, my PartnerId usually loads, now nothing

 

[Minty]

I have to ask why not use an autonumber ?

 

[arnelgp]

Where did you put the functuon, in module or class. Put it in standard module.

 

[arnelgp]

It is much easier if you make the form Bound to Partner table and set Data Entry property of the form to Yes (Property->Data->Data Entry)

 

[Gr3g0ry]

i tired the whole bounded form thing and was getting errors, especially when i tried closing a form, it would write all the data back to the tables, corrupting my data. so i chose the unbounded method. unbounded works for me because i have some functionality that works great for me.

i dont want to use autonumber at all. im trying my best not to use it. if all else fails, i may just resort to it, but until then ...

please see attached file.

the name of the form is frmAddNewPartner.

id soo love some help.

 

[Pat Hartman]

i tired the whole bounded form thing and was getting errors, especially when i tried closing a form, it would write all the data back to the tables, corrupting my data. so i chose the unbounded method. unbounded works for me because i have some functionality that works great for me.

First of all, it's Unbound not Unbounded.
You were getting errors because you didn't understand how to use form events to control the actions of the form. Access is a Rapid Application Development tool and the most RAD part of Access is bound forms. If you don't use bound forms, you should probably not even be using Access since you are having to deal with all the bad parts without the benefit of the RAD parts.

The FORM's BeforeUpdate event is the LAST event that runs prior to a record being saved. This event ALWAYS runs if the record is dirty (needs to be saved) and there is no way to bypass it. It runs regardless of whether you issue a save command or if Access decides the record needs saving. The majority of your validation belongs in this event and you can cancel the event by simply saying:

Cancel = True
Exit Sub

Hopefully you get paid by the hour because you are causing yourself a whole lot of unnecessary work.

 

[arnelgp]

You are not calling the function, here is the fix.

 

[Gr3g0ry]

Pat Hartman, thanks you need not be so savage. i had initially said that im new to access, i had also said in trying to learn this thing. thanks for your input re the First of all, it's Unbound not Unbounded. Cancel = True
Exit Sub. thanks.

arnelgp ... you're a life saver and a real BOSS. thanks for your patience and understanding bruh.

ill try your fix

 

[arnelgp]

Goodluck with your project.