Run-time error '2498' when adding a new record from a subform (1 Viewer)

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
I have a main form with several subforms in tabs. From one of the subforms I list linked records to the main form (Clients) within that subform (Bank account details). I list the records and have a button to add new records.
Where the Client has one or more records in the subform the add button works perfectly.
When the subform has no records the add new records button produces the following error
"Run-time error '2498'
An expression you entered is the wrong data type for one of the arguments"

The add button has
Private Sub Command52_Click()
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId
End Sub

the "AddClientBankDetailsFrm" popup form has

Private Sub Form_Load()
If IsNull(Me.OpenArgs) = False Then
MsgBox "Form was opened with ClientID = " & Me.OpenArgs
Me!ClientId = Me.OpenArgs
Else
MsgBox "No ClientID was passed."
End If
End Sub

ClientId is the primary key of the main form and the secondary key in the new record

Any help would be appreciated

Atrium
 

MarkK

bit cruncher
Local time
Today, 15:07
Joined
Mar 17, 2004
Messages
8,178
What line causes the error?

Or, if you have a main form with subforms, and you open that main form in Data Entry (acFormAdd) mode, then the Primary Key of the parent record will be null, and the sub forms will fail to link. This error might be that failure.

One thing you can do then is add the record first, then open the form in edit mode, and go to that record. Probably the easiest way to do that is open a recordset, like . . .
Code:
dim newID as long

with currentdb.openrecordset("yourTable")
   .addnew
   !foreignKey = Me.ClientID
   newID = !PrimaryKey
   .update
   .close
end with

DoCmd.OpenForm "SomeForm", wherecondition:="ClientID = " & newID
So do you see what happens there? The new record is added and you keep a copy of the newID, then open the form in Edit Mode, and find that new record.

Or you can load the subforms programmatically, and don't load them if their linking field is null. To do this, delete the SourceObject property of the subform control, and run a test in Form_Load, to see if it's safe to load the subforms . . .
Code:
Sub Form_Load()
    If Not IsNull(Me.PrimaryKey) Then
        Me.SomeSubformControl.SourceObject = "fNameOfForm"
    End If
End Sub
. . . but then you need to check on after_insert or after_update that you can load the subform when the record is eventually added.

Does any of that make sense to you?
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
Thanks very much for that I will look into it over night.
Very grateful
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
The main form and the subforms all display the correct records - no problems there.
This is the line that causes the problem
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId

It doesn't appear to even get to the "AddClientBankDetailsFrm" form

As I said before the strange thing is that there is no problem if there's already a record displayed for this client within this TAB. The Add New Record button works as it should. BUT when you try to add the first record for this TAB for this parent form I get the error

Sorry to be pain guys I'm sure it's something simple but I just can't see it

Cheers
 

JHB

Have been here a while
Local time
Today, 23:07
Joined
Jun 17, 2012
Messages
7,732
This is the line that causes the problem
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId
Only for info, the "acFormAdd" is not in the right argument place.

Code:
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , acFormAdd, , OpenArgs:=Me!ClientId
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
Same error, I changed the
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , acFormAdd, , OpenArgs:=Me!ClientId

as you suggested but it produces the same error as before

:mad:
 

MarkK

bit cruncher
Local time
Today, 15:07
Joined
Mar 17, 2004
Messages
8,178
Did you read this in post #2?
if you have a main form with subforms, and you open that main form in Data Entry (acFormAdd) mode, then the Primary Key of the parent record will be null, and the sub forms will fail to link.
Have you understood this and ruled it out as the problem? I offered work-arounds if this was the problem. Did you try one?
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
Thanks Mark
I open the parent form using acFormEdit
As I said before all of the subforms display all of the correct records linked to the parent key.
The error only occurs when one of the subforms doesn't have any records to display and the "Add Record" button is clicked

If a subform has displayed linked records and the "Add Record" button is clicked then it all works perfectly.

I have about seven tabs with subforms of which 4 have "Add Record" buttons on them.

Cheers
 

MarkK

bit cruncher
Local time
Today, 15:07
Joined
Mar 17, 2004
Messages
8,178
But you are saying that this is the code that causes the error, correct?
Code:
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
Yes that's the one that is highlighted in yellow in the debugger

Private Sub Command52_Click()
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , acFormAdd, , OpenArgs:=Me!ClientId
End Sub
 

MarkK

bit cruncher
Local time
Today, 15:07
Joined
Mar 17, 2004
Messages
8,178
And what is the error description? When I raise 2498 I get "Application defined or object defined error." Not that helpful.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:07
Joined
Feb 19, 2013
Messages
16,553
It is not usual to mix your parameters

have you tried

docmd.OpenForm "AddClientBankDetailsFrm",acNormal,,,acFormAdd,,Me!ClientId
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
Yes I have tried that - same result

The message with the error is - An expression you entered is the wrong type for one of the arguments"


Would it have something to do with

My mainform has all the Client details and the field ClientId would have the right value
My subforms with linked child records would have ClientId with the right value
BUT for those subforms where there are no child records for the main form what would the value of Me!ClientId be? - would it be null

If so I would be trying to pass a null value and creating problems
 

MarkK

bit cruncher
Local time
Today, 15:07
Joined
Mar 17, 2004
Messages
8,178
Yes, I can cause the same error with the same code if my OpenArgs parameter references a control with a null value. So there's your trouble. Check the value of the ClientID before trying to open the form.
Code:
if not isnull(me.clientid) then
   docmd.openform "test123", openargs:=me.clientid
end if
 

atrium

Registered User.
Local time
Tomorrow, 09:07
Joined
May 13, 2014
Messages
348
Thanks guys you have been more than helpful.
Using your direction i have solved the problem by

Private Sub Command52_Click()
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , acFormAdd, , OpenArgs:=Forms![ClientFileFrm]![ClientId]
End Sub

It all works thanks a heap - I'm only new to this forum I'm sure I'll be logging on many times in the future.

Cheers from the rainy Gold Coast today

:)
 

Users who are viewing this thread

Top Bottom