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!
Last edited by Bazzlad; Oct 28th, 2003 at 08:25 AM.
*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!
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
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));
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.
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.
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.
Opening a Report reliably-Not my SQL statement?
Reply With Quote
