Macro to Open another Form for Current Record

[veraloopy]

Hi everyone

I have a macro which looks at the primary key (this being 'CustRef') in the current form and opens up the 'Finance' form retrieving the same 'CustRef' record.
Here is my macro:

Private Sub Finance_Click()
On Error GoTo Err_OpenFinance_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Finance"

stLinkCriteria = "[CustRef]=" & Me![CustRef]
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize

Exit_OpenFinance_Click:
Exit Sub
Err_OpenFinance_Click:
MsgBox Err.Description
Resume Exit_OpenFinance_Click
End Sub


This is great if the record already exists in the 'Finance' table/form, but when the record does not exist, the database just gives a blank 'CustRef' field.
I need the 'CustRef' field in the Finance form to automatically populate with the 'CustRef' number even though the record does not exist.
This would be so that the record could be created without having to rely on people selecting the correct customer number.

Thanks in advance for you help

 

[JoanneJames]

you could try setting the default value for that field to the value from the prevous screen

 

[veraloopy]

Sounds like a good idea - how do I do that??

Sorry I'm a bit new to access and although I have found my way around, these little things always end up causing the most problems

Thanks in advance for your help

you could try setting the default value for that field to the value from the prevous screen

 

[JoanneJames]

In current event, change the fields default property: ie:
if me.newrecord then
field.DefaultValue= Me![CustRef]
endif

 

[veraloopy]

Hi
When I try to add the code below into the Default Value field, it only allows me to add an expression and not go into VBA...

Still can't get this to work

In current event, change the fields default property: ie:
if me.newrecord then
field.DefaultValue= Me![CustRef]
endif

 

[JoanneJames]

You have to create code under the current event (OnCurrent). Select the form. Click on the square box, upper left. Right-click & select properties. On Event tab, select "On Current". Select event procedure, then select ... Enter your code & debug.

 

[veraloopy]

Hi
Thanks for this, I finally found the event tab, etc and have added the code in but when I click into it, I get an error "Run-time error '94' Invalid use of Null

Any ideas?
Cheers

 

[veraloopy]

Sorry, to make things easier... this is what I have in VB

Private Sub Form_Current()
If Me.NewRecord Then
[CustRef].DefaultValue = Me![CustRef]
End If
End Sub

The field that i need the Custref to appear in is called [CustRef]

Thanks again

Hi
Thanks for this, I finally found the event tab, etc and have added the code in but when I click into it, I get an error "Run-time error '94' Invalid use of Null

Any ideas?
Cheers

 

[boblarson]

Private Sub Form_Current()
If Me.NewRecord Then
[CustRef].DefaultValue = Me![CustRef]
End If
End Sub

That code makes absolutely no sense to me. If you are on a new record then CustRef will be null, so you are trying to assign a null value to CustRef's default by doing so.

Instead of the On Current event, put your Default Assignment in the After Update event of the form (I believe that will work).

 

[JoanneJames]

The reference to [CustRef] should come from the first screen that HAS the value: instead of me!custRef, would be forms!frmName!custref

 

[veraloopy]

OMG That's worked!!
Thank you, thank you thank you!!
Really really appreciate all your help

 

[JoanneJames]

Your welcome. Happy coding...