THe dreaded #Name? is ruining my form!!!!

[chrisjames25]

Hi All. Been pulling my hair out today and just cant see what doing wrong. I have a mainfomr with a subform.

THe subform is populated once i click a command button in the mainform and the subform is based on a qry that looks at an input in a textbox on the mainform.

SO far so good. I open the form, i choose some sales data and press a command button and the data neatly populates. Perfect.

Once finished with the data I click another commnad button which clears the txtbox from the mainform and refreshes the subform.

            Me.Txt_SalesOrderNo = ""
            'Me.Txt_SalesOrderNo.Value = vbNullString
 
            Me.Frm_SubPotting.Requery

It is at this point that i have the problem. I want the subform to now reset to no data but instead of no data I get rows of controls saying #Name? instead. Image attached.

I beleive it may be down to my query design and in particular the way i look at the control from the mainform but im not 100% sure.

Attached is also a copy of what the query design looks like.

As ever any help massively appreciated. Currently having to close and reopen form everytime which isnt ideally and user friendly.

 

[mike60smart]

Hi All. Been pulling my hair out today and just cant see what doing wrong. I have a mainfomr with a subform.

THe subform is populated once i click a command button in the mainform and the subform is based on a qry that looks at an input in a textbox on the mainform.

SO far so good. I open the form, i choose some sales data and press a command button and the data neatly populates. Perfect.

Once finished with the data I click another commnad button which clears the txtbox from the mainform and refreshes the subform.

            Me.Txt_SalesOrderNo = ""
            'Me.Txt_SalesOrderNo.Value = vbNullString

            Me.Frm_SubPotting.Requery

It is at this point that i have the problem. I want the subform to now reset to no data but instead of no data I get rows of controls saying #Name? instead. Image attached.

I beleive it may be down to my query design and in particular the way i look at the control from the mainform but im not 100% sure.

Attached is also a copy of what the query design looks like.

As ever any help massively appreciated. Currently having to close and reopen form everytime which isnt ideally and user friendly.

Hi Chris
Are you able to upload a copy of the database?

 

[theDBguy]

What does the code for your first button look like? If it has something like this:

Me.SubformControlName.SourceObject = "something"

Then your second button could have something like this:

Me.SubformControlName.SourceObject = null

 

[CJ_London]

Me.Frm_SubPotting.Requery

not in a position to confirm but suggest try

Me.Frm_SubPotting.Form.Requery

other questions

is me.Txt_SalesOrderNo bound or unbound? I would expect the latter, but if bound, what is the datatype of the field it is bound to?

and why are you using eval in your query criteria?

and what is the datatype of salesorderID you are applying the criteria to? implication is it is numeric in which case you are then trying to compare to a string in your query - and the eval function will generate an error for a zls anyway.

 

[ebs17]

My thoughts:

?? Me.Txt_SalesOrderNo = ""
I wouldn't assign text to a number, but rather a number or NULL (the database can handle NULL excellently).
Me.Txt_SalesOrderNo = Null

As a criterion in the query
?? Eval(Forms!...)
? Eval("") --- produces a crucial error

 

[The_Doc_Man]

Whenever you get the dreaded "#name" error, you have spelled something wrong OR you have incorrectly qualified where it is. From the exhibit where ALL of the columns show #name, I suspect that you are trying to reference something from the wrong place. One column of #name would be spelling. Multiple columns? Location errors.

 

[chrisjames25]

HI All, thanks for so many helpful responses. Blows my mind how helpful this forum is.

The solution came by

What does the code for your first button look like? If it has something like this:

Me.SubformControlName.SourceObject = "something"

Then your second button could have something like this:

Me.SubformControlName.SourceObject = null

Hi. Thanks for this. I tried making this switch and used this code

            Me.Txt_SalesOrderNo = Null
            'Me.Txt_SalesOrderNo.Value = vbNullString
 
            Me.Frm_SubPotting.Requery

And it now works a treat. Superstar.

 

[chrisjames25]

My thoughts:

?? Me.Txt_SalesOrderNo = ""
I wouldn't assign text to a number, but rather a number or NULL (the database can handle NULL excellently).
Me.Txt_SalesOrderNo = Null

As a criterion in the query
?? Eval(Forms!...)
? Eval("") --- produces a crucial error

Thanks also the null switch worked perfectly.

 

[chrisjames25]

not in a position to confirm but suggest try

Me.Frm_SubPotting.Form.Requery

other questions

is me.Txt_SalesOrderNo bound or unbound? I would expect the latter, but if bound, what is the datatype of the field it is bound to?

and why are you using eval in your query criteria?

and what is the datatype of salesorderID you are applying the criteria to? implication is it is numeric in which case you are then trying to compare to a string in your query - and the eval function will generate an error for a zls anyway.

View attachment 112471

I'll be honest I'm not sure why i am using a Eval. I think i was stuck last week getting the query to work and i saw on a forum page page that it was something to do with the format needing to be a string so it recommended using the eval. WHen i used the eval my eval worked as i wanted.

Sadly in access with my knowledge I dont alwasy understand what I have done but am happy it has solved a problem. I do keep trying to learn though which is the main thing

Remembered why i used eval - it was because i needed to run an update qry on the above qry and it wouldnt run as found no results. But when switched to using eval it did. NOt sure why but remember that is why i was searching the forum.

 

[theDBguy]

HI All, thanks for so many helpful responses. Blows my mind how helpful this forum is.

The solution came by

Hi. Thanks for this. I tried making this switch and used this code

            Me.Txt_SalesOrderNo = Null
            'Me.Txt_SalesOrderNo.Value = vbNullString

            Me.Frm_SubPotting.Requery

And it now works a treat. Superstar.

Glad to hear you got it sorted out. Good luck with your project.