Why am I unable to assign a value to a textbox?

[Deleted member 73419]

Hi,

I have a form which contains a couple of textboxs, buttons etc. Quite simply, I am trying to assign a value to the textbox when the form opens but I am getting the following error message:

run-time error 2558:
You can't assign a value to this object

The code driving this is "quite" simple for what I'm asking it to do:

Private Sub Form_Open(Cancel As Integer)
    Dim strArgs() As String
    
    NavigationButtons = False
    RecordSelectors = False
    
    If (Not IsNull(OpenArgs)) Then
    
        strArgs = Split(OpenArgs, ",")
        
        Select Case strArgs(0)
            Case "New"
                txtDate.Locked = False
                txtDate.Value = Format(Now, "dd mmm yyyy")
                txtCreatedBy.Locked = False
                txtCreatedBy.Value = NTDomainUserNameFormatted
        End Select
        
    End If
        
End Sub

The code opening the form is:

DoCmd.OpenForm "frmNewPartNumber", acNormal, , "PartNo='" & vCreateNextNumber.NewNumber & "'", , , "New"

So looks fairly simple .
I have checked Data Entry, Allow Additions, Allow Deletions etc these are all set to yes - looking around these seem to be responsible for some of these types of errors alas not on this occasion...

Can anyone shed any light on what is causing this?

Thanks

 

[missinglinq]

Which line does the code break on?

What is the Datatype of the Fields Bound to txtDate and txtCreatedBy?

Linq ;0)>

 

[Deleted member 73419]

Which line does the code break on?

What is the Datatype of the Fields Bound to txtDate and txtCreatedBy?

Linq ;0)>

doh!! missed highlighting the obvious :banghead:
It breaks on:

txtDate.Value = Format(Now, "dd mmm yyyy")

txtDate is bound to column with data type 'Date/Time' and txtCreatedBy is bound to column with data type 'Short Text'.

 

[missinglinq]

So txtDate is bound to a Date/Time Field...but you're trying to assign a Value to it using

txtDate.Value = Format(Now, "dd mmm yyyy")?

I suspect that the problem with that is that

Format(Now, "dd mmm yyyy")

returns a String/Text, not a Date/Time value.

Linq ;0)>

 

[JHB]

Place your code in the Load event instead of the Open event, then the Load event is executed after the Open event.
You don't need the .Value property, but use the Me. when you refers to a control on a form.
Ex.

Me.txtDate = Format(Now, "dd mmm yyyy")

 

[arnelgp]

as Missinglinq explains, it returns a String.
if you wanted to Format your txtDate textbox,
you do it on Load/Open event of the form:

Private Sub Form_Load()
me.txtDate.Format = "dd mmmm yyyy"
End Sub

 

[missinglinq]

...Place your code in the Load event instead of the Open event...

Good catch...I missed that! FYI, for the OP...the Form_Open event is too early to do anything that involves the Form's data...you have to use the Form_Load event, as JHB said.

Linq ;0)>

 

[Deleted member 73419]

Hi guys,

Thanks for your suggestions however changing to the Open event sort of fixed the issue which I posted about however caused further complications namely filtering.

My way of thinking is just to strip the whole thing back to basics - I figure if I cannot get a simple form to open then I'd might as well call it a day!!

So, I have literally stripped everything back to just two forms, the essence of which is what I am trying to do here; open a form which is filtered on a specific record in a table. Sounds simple enough but I cannot even get this to work.

All I get is a Type Mismatch error.. I've put the DB as an attachment. I get this when I click on the button on Form1. It is as simple as I can get it - one button with the code behind it being:

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Form2"
    DoCmd.OpenForm stDocName, , , "[PartNo] = '123457'", stLinkCriteria

Exit_Command35_Click:
    Exit Sub

Err_Command35_Click:
    MsgBox err.Description
    Resume Exit_Command35_Click
    
End Sub

The type mismatch occurs:

DoCmd.OpenForm stDocName, , , "[PartNo] = '123457'", stLinkCriteria

 

[Mark_]

Is [PartNo] a string?

If not, then it should not be "[PartNo] = '123457'", it should be "[PartNo] = 123457".
Also are you sure that is a good part number?

 

[JHB]

..

Private Sub Command35_Click()
..
    DoCmd.OpenForm stDocName, , , "[PartNo] = '123457'"[B][COLOR=Red], stLinkCriteria[/COLOR][/B]
..

The marked text is wrong, remove it.

 

[Deleted member 73419]

Is [PartNo] a string?

If not, then it should not be "[PartNo] = '123457'", it should be "[PartNo] = 123457".
Also are you sure that is a good part number?

In this demo case, 123457 is a string. In the PN table, the PartNo column is Short Text.
The reason for this is that PartNo will not always be numerical; in a real life case it could be AB-1234, ABC-DEF so I figured that a column with text data type would be the way to go....

@JHB - removing the highlighted text makes no difference. I get the same result; the form opens but unfortunately there is no filtering.

 

[JHB]

..
@JHB - removing the highlighted text makes no difference. I get the same result; the form opens but unfortunately there is no filtering.

Ofcause it does a difference, you get an error if you don't remove it.
It is not filtered because you've set the form to data entry so it goes to a new record.