Form opened in form view is completely blank 2

[steve728]

I'm using Access 2000. Just 1 of my forms is giving me grief. The backend recordsource is not providing the form with a record to display on the form, therefore all is blank. I'm using a finction in one of the criteria fields to filter out the record I want displayed. (i.e. Getpo())
In the open event of the form I used msgbox Getpo() to make certain that the value is correctly being entered into the query. It is. I verify that the correct record is being selected by clicking of the dataview for the query. The record is there. I made sure that the header/footer is not too big. I'm not using any query joins. The filter & orderby filter is blank. I noticed that the form displays perfectly if I click on the Record menu / Remove Filter/Sort option. The form works perfectly if I close the form and immediately double click on it where it is stored in the form tab of the database container. Please help me. This problem is really setting me back!

I'll appreciate any help you can offer.

Steve

 

[ksgirl2]

try copying the entire form in design view and pasting it to a new form that you name something else. then delete your old form then rename the new one what you had the old one (so you don't have to change anything else) ... try that.

good luck.
it works for me occasionally.

marie

 

[KornGeek]

Just a slight change from what ksgirl2 suggested. Rather than deleteing your old form, I would rename it along the lines of MyForm_old until you have this resolved. That way, if for some reason you need to refer back to it, you still have it.

Is the data source of the form set to the stored query, or to some SQL based on the stored query? If it's the SQL, it is possible that your problem lies somewhere in the SQL code.

 

[puic]

sometimes access forms need refresh. try to insert in the code for afterupdate of the control you work with something like

"....
me.refresh
..."

Also be sure you haven't another instance of the form running as a subform or so.

 

[steve728]

I tried that already. It didn't work.

Thank you for replying.

Steve

 

[steve728]

Wow! I didn't realize I had so many replies. Thank you friends! Yes, i tried the creation of another form and pasting the controls. Yes, I made a backup of the original form. I even tried making an abbreviated form with fewer textboxes. The backend query is not SQL code, it's a query built wih the Access interface. The dataviiew of this query diplays the filter record OK yet the form does not.
When I close the form and double click on the form inthe database container it works perfectly. I just tried the me.refresh idea in the afterupdate event. It didn't help. I really appreciate your efforts. What could be the problem? Another clue is, all my screens worked fine when I used DAO. I want to use ADO now because of eventually using this code in web applications and Visual Basic.net.

Steve

 

[KornGeek]

The first thing I would try is to switch back to DAO. If this fixes the problem, then you have narrowed down where to search for the root of the problem. If this doesn't fix it, then you've eliminated one possibility. Because this seems like the most likely failure point, I would try it first.

Also, I noticed that you mentioned your form works if you close it, and then reopen it from the database window. How are you opening it otherwise?

Because you can get it to display correctly by removing filters, I'm guessing that somewhere (possibly in code) you might be applying a filter to the form.

You might also try putting some debug message boxes in to test the record count in a few places (using DCount or something along those lines).

 

[steve728]

KornGeek, you & puic are the only ones in the cyberspace universe that have really tried to help me! I'm opening the form with docmd.openform "Myform". This code is in a public sub in a general module. The backend select query has a function (Getponum()) in the criteria. In the openform event I'm using msgbox getpponum(). The function is retrieving the proper value alright. I'm not using any filters.

Thanks again buddy!

Steve

 

[KornGeek]

I would investigat the DAO possibilty. That seems to be the most likely source.

If that fails, my next best guess would be to recreate the form from scratch. More than once I have done something to a form and had it create other side effects. It's possible that something else you are doing that seems unrelated is causing this.

Beyond that, I'm stumped.

 

[steve728]

Thank you friend! I'll try to take it from here

Steve

 

[RickSpr]

One reason a form will open blank is that its recordset is empty. Are you providing a filter or criteria parameter on your OpenForm? Perhaps your criteria is in error and is eliminating all the records. That would explain why it works right when you open it from the Database Window--there's no filter then. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[steve728]

I verified that the Getponum() function is returning the correct value with msgbox in the openform event. Also, when I change the form to design view and choose dataview the correct record is displayed. I f I immediately then click back to form view the form displays correctly. Doesn't this tell me that the backend recordset is gathering data properly but somehow the form is not initially sycronizing with the backend query.

Thank your advise thus far. I against a brick wall.

Steve

 

[RickSpr]

I agree that the switch to datasheet view and back to form view suggests your recordset contains a record.

Going back to reread, I notice you said you're doing the MsgBox() in the Open event. I think you need to do it in the Load event. That's what I use when I need to set the RecordSource at the time the form is opened. See if that works. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[steve728]

I tried what you said. I changed the msgbox from on open to on load. The function yields the correct value and the problem still continues.

Thank you so much for giving this advise.

Steve

 

[RickSpr]

I'm at a loss. I guess the next step is for you to show us your code. Ideally, I'd like to know the design-time settings of the relevant form properties, too. If the RecordSource refers to a query, I'd like to know the query's SQL statement as well. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[steve728]

Backend Select Query SQL view:
SELECT APP.VNO, APP.PO, APP.TERMS, APP.BALDUE, APP.DESC, APP.CONTACT, APP.SHIPVIA, APP.TRACKING, APP.DUEDATE, APP.NET, APP.SHIPTO, APP.ADDRESS1, APP.ADDRESS2, APP.ADDRESS3, APP.FOB, APP.DISCOUNTD, APP.DISCOUNTP, APP.JOBNO
FROM APP
WHERE (((APP.VNO)=Getvno()) AND ((APP.PO)=GetNextInvoice()));

Public Function Getvno() As String
Getvno = mAPNumber
End Function

Public Function GetNextInvoice() As String
GetNextInvoice = mNextInvoice
End Function

Form properties:

Filter = ""
Orderby = ""
AllowFilters = Yes
DefaultView = Single Form
ViewsAllowed = Form
AllowEdits = Yes
AllowDeletions = No
Allowadditions = No
DataEntry = No
RecordsetType = Dynaset
RecordLocks = Edited Record
RecordSelectors = No
Popup = No
Modal = No
Borderstyle = Dialog
Controlbox = Yes
Cycle = Current Record

I hope this gives you a clue.

Thanks again,

Steve

 

[RickSpr]

We've been talking about a MsgBox function to get the parameters, but I assume you really used an InputBox() function. I also wanted to see your Form_Open or Form_Load event procedure, but I assume it just uses InputBox() to assign user responses to mAPNumber and mNextInvoice, and that those are public module-level String variables in a standard module.

I set up a form to do this and had similar results, at least initially--no record in the form. But when I closed the form and opened it again, I got the record that matched my input from the first time.

I put Debug.Print statements in the InputBox function and in the Form_Load event procedure, and discovered that the function call by the query ran before the Form_Load event. Thus the query is fetching its parameters before the variables are being set. That would explain the problem.

Here's what I would do. Eliminate the query's WHERE clause. Modify the Form_Load event procedure to something like this:

    Dim strAPNumber As String, strNextInvoice As String

    strAPNumber = InputBox("Enter AP Number")
    strNextInvoice = InputBox("Enter next invoice number")
    Filter = "VNO='" & strAPNumber & "' AND PO='" & strNextInvoice & "'"

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[steve728]

Thank you so much! All this makes so much sense. I'll let you know what I come up with.

Steve

 

[steve728]

Yep! You're a GENIUS! It worked Man! All these futile hours are over! I've never had this problem before. Why is the sycronization of the backend query not always working on a timely basis? Earlier I even recreated a new form and still it didn't work. I have other forms that do work.

Thanks again Buddy! Now let's pray for our soldiers in Iraq!

Forever grateful!!!!

Steve

 

[RickSpr]

To me the question is "Why does it work sometimes?

Probably what's happening is that the preparation and opening of the query occurs on a separate thread--that is, in parallel with the opening of the form itself. If something delays the query thread, the InputBox functions may get finished in time to make it work. This might happen with, for example, a more complex query involving joins, especially if there are many levels of intermediate results. A multiple-thread situation where results depend on which thread runs fastest is called a "race condition", and is among the hardest bugs to find.

Glad you thought my post was helpful. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein