Opening a Report reliably-Not my SQL statement?

It's in VBA (Access)
I have a report which grabs data off an open form *Through a query*
[Query grabs data off open form-report shows Querys data]

The problem is it doesn't always open, I get messages like:

access can't find the field FORMS reffered to in your expression
or
the open report action was cancelled.

The problem is, it seems to be totally random, it won't work, and then I'll reopen and it will/won't. There doesn't seem to be any way to 100% guarentee opening it,,,
and it needs to be.
The code I'm using:

VB Code:

'Checks if needed field is filled in
    If not Datebox.Value >= "" Then
    MsgBox "Please enter a Date"
    Datebox.SetFocus
    Else
    'Checks if the form sale is the only open form
    For Each frm In Forms
    If frm.Name <> "frmsale" Then
    DoCmd.Close acForm, frm.Name
    End If
    Next frm
    'Hides the listbox, goes forward a record then 
    'backwards a record-to make sure the data is 
    'saved then re shows the listbox
    List106.Visible = False
    DoCmd.GoToRecord , , acNext
    DoCmd.GoToRecord , , acPrevious
    List106.Visible = True
    'Opens report
    DoCmd.OpenReport "qryreport", acViewPreview

PLEEEEEEEEEEASE Help, It's been driving me mad for a week or longer now!

Doesn't anyone have ANY ideas? Maybe a different way of opening the form? Come on guys...

Sorry, I don't have a clue about VBA :(
It's a stupid language and I find it very complex and very hard to understand *sob*

Woka

your code doesn't make sure that the form is open, only that no others are.

how about this instead of your current For loop:

VB Code:

Dim IsVisible as Boolean
IsVisible = False
For Each frm In Forms
    If LCase(frm.Name) <> "frmsale" Then
      DoCmd.Close acForm, frm.Name
    Else
      IsVisible = True
    End If
Next frm
 
If Not (IsVisible) Then 
  'show the form here  (sorry, dont know Access code for it!)
End If

*Cries harder*
It's so dumb. I could handle it if the error was consisitent. But half the time if the report doesnt load, I knock the form into design mode and back and it works. Although sometimes that doesn' work. GRRRRRRRRR
Thanks anyway Woka!

Quote:

---

Originally posted by Wokawidget
Sorry, I don't have a clue about VBA :(
It's a stupid language and I find it very complex and very hard to understand *sob*

Woka

---

I'm with you on that one... even your (previously impossible) Multithreading code is easier to work with :eek:

That code works great Si,

VB Code:

Dim IsVisible As Boolean
    IsVisible = False
    For Each frm In Forms
    If LCase(frm.Name) <> "frmsale" Then
    DoCmd.Close acForm, frm.Name
    Else
      IsVisible = True
    End If
    Next frm
 
    If Not (IsVisible) Then
    DoCmd.OpenForm "frmsale"
    End If

But doesn't stop the problem.
I think the problem may be with the way the report is opened....
Maybe try making sure the report is shut before I open it...
? Damn VBA is retarded.
Maybe I'll try shutting the report

Do you think as it can't find the field FORMS
{which is stated in my SQL Statement}
That there may be a problem in my SQL?
Here it is:
Ignore the bracket attack, MS added those for me.

Code:

---

SELECT DISTINCT [tblin].[Make], [tblin].[Phone Model], [tblin].
[IMEI], [tblin].[Sold?], [tblin].[Sale Number], [tblin].[Price],
[tblcustomer].[Customer Name], [tblcustomer].[Company],
[tblcustomer].[Customer ID], [tblout].[Date]
FROM tblcustomer, tblin, tblout
WHERE ((([tblin].[Sold?])=Yes) And (([tblin].[Sale Number])=
[Forms]![frmsale]![sale number].value) And (([tblcustomer].
[Customer ID])=[Forms]![frmsale]![customer ID].value) And
(([tblout].[Date])=[Forms]![frmsale]![Datebox].value));

---

Anything wrong with that?

I think it's ok, just make sure that the values on the form (eg:[sale number].value) have appropriate values.

Quote:

---

Originally posted by si_the_geek
I'm with you on that one... even your (previously impossible) Multithreading code is easier to work with :eek:

---

My code makes perfect logical sense, well easy to follow. I find code like that dead easy to work with. Now VBA on the other hand :(

Wa

Nothing is working.
I've tried opening the actually query in the BG, refreshing all the data on the form, had a look at my SQL making sure the correct forms amd data are there and still it works sometimes and sometimes not.
I'm gonna kill something/one.
Is there like, an add on program for reports-something reliable?
LONG SHOT.

If I post the project on here, would anyone be willing to have a look through it and try to find the fault?
Bazz

Just post it anyways.
I won't be able to help as we don't have Access installed at work :(
Will be able to have a look when I get home, however I don't know if I would be of any use.

Woka

Here it is.
The add sale and find sold forms are the problems, as I said, sometimes they'll show the invoice (report) sometimes they won't.

*please ignore the goto's and .values - it's the only way I can get VBA to do certain things. Trust me on that.*

if anyone wants to dl and have a look feel free, the offer is open purely to Woka!

Quote:

---

Originally posted by Wokawidget
Sorry, I don't have a clue about VBA :(
It's a stupid language and I find it very complex and very hard to understand *sob*

Woka

---

The "language" of VBA is VB. So you can't say the language is stupid without saying VB is stupid. The only difference in VBA is that the MS Office application class s exposed. So you would have to say that MS Office application classes are stupid. They are complex because they dodoes of (great) stuff. But you can say the classes are stupid if you want. One thing that is stupid is MS Access. It seems to be designed for non-programmers to handle databases. Of course, good Access developers can do wonders with Access. The problem is that everything gets spread out over tables, queries, forms, macros, VBA, etc. Access leans heavily toward bound controls, and wit all these objects that is very contusive to spaghetti code (without the code even!) This seems to be the case in this...um...case.

frmSold has a control (List33) that queries qyrnewsold, which in turn has fields that are derived from frmSold. You get a circular reference. If frmSold is not open, List33 cannot query qyrnewsold because the frmSold values it depends on for the query are not set. So it will ask you for the values, and if it doesn't get the values it need it raises the error messages you have been getting.

The solution is to kill the circular reference. It appears Command149 should create a report based on the values in frmSale. Simply replace the three instances of frmSold in qyrnewsold with frmSale. Now qryrereport is not dependent on values in frmSold when it queries qyrnewsold so you have eliminated the circular reference. If you need List33 to be specifically dependent on the values in frmSold, make one query to be used exclusively by frmSold dependent on frmSold control values, and another query to be used exclusively by used qryrereport dependent on frmSale values. :)