Very strange issue: form opens blank

[spleewars]

Ok, my question seem rather to be either unresolvable. I have posted the following post on other forums to be honest but none replied or came with a decent idea:



I've been struggling several time with this problem and apparently its driving me a little crazy: i have this code

Private Sub CodeNumber_DblClick(Cancel As Integer)
  DoCmd.OpenForm "Products", , , _
        "[products.codenumber]=" & "'" & Me.CodeNumber & "'"
       
   End Sub

I cannot understand why on other forms this code works flawlessly and sometimes on others no. I have used this sort of code many other times on lots of other forms and worked properly but in this one, nope! The form automatically open on double click and where it is suppose to load the record, the form 'products' open itself with no loaded record.

Further testing made: removed all code and enabled and unlocked some disabled fields from form Products, changed "'" with "", "#" and tried this code as well:

"[products.codenumber]=" & "'" & Forms![search_advancedstock]![search-advancedstock-datasheet].CodeNumber] & "'"

again, Products form open but no record information is loaded...

What is wrong?

[jzwp11]

What is the name of the control on the Products form that holds the code number? What is the name of the field in the record source that holds the code number? What is the datatype of that field?

[jzwp11]

Regarding [products.codenumber], if the table name is products and the field name is codenumber, the correct syntax is [products].[codenumber] or just leave off the square brackes altogether since you do not have spaces in the names.

[John_G]

Hi -

If you are seeing a completely blank form, without even blank fields showing, it means that no records matched the criteris you specified, i.e. "[products.codenumber]=" & "'" & Me.CodeNumber & "'" - there were no products with that code number.

How you get around it depends on what you want to do; if all you want to do is look at the product list, then do a dcount to find out if there are any records; if there are none, display a message and do not open the form.

If you want to add records and there are no existing ones, switch the form to data entry mode just after opening it, with DataEntry = True.

If you are seeing the form with blank fields, and you know there is data that should be shown, the form may already be set to Data Entry = True (Yes), in which case it will never show existing data.

HTH

John

[spleewars]

Thanks guys of your input. Actually thanks to jzwp11 i became conscious that everything was passing through a very complex query and its statement was not reversible to make things work as i meant to. I solved the problem by creating a new query and setting new statements where required.

Again guys, thanks and have a great weekend

[jzwp11]

You're welcome.