Need help VBA and Access

[QuantumCat]

Okay -I'm in a hurry;
I need help BAD. I am very noob at this. I am very acquainted with VB,
but proved a noob at VBA in Access. I 'm not asking for you to do the writing, but I need examples and links and tutorials, and since time is limited I don't have time to skim through all the hits I get hoping that one of the many will be the solution to my problem.

For my work I have been asked to do this:

-I need to make a report (fixed layout with cell-like input boxed, for instance
like the VB text box)
-This input is conditionally free (free input but checked for validity) or limited
by top down menu's
-On input of some of these fields (lost-focus) others should be filled out with
conditional statements (IF such and such THEN ...)
-These reports should be saved in a separate table which have to be
accessed when asked for to either change /update/delete or add a report
-Access existing tables in Access (SQL)

The top down menu's should be filled from the existing tables some.

So a few questions:

-So methinks I make a query for getting the report?
-But how do I save it?
is there a way to e.g. call a report by query
have the report filled and have a " save" button to save it?
-I make textboxes in the report, how do I access them through VBA?

many thanx in advance

QC

 

[namliam]

-So methinks I make a query for getting the report?

Yep you make a query for your report

-But how do I save it?
is there a way to e.g. call a report by query
have the report filled and have a " save" button to save it?

Simple create the query in design view, save it and select it as your report datasource

-I make textboxes in the report, how do I access them through VBA?

No, make a seperate form with textboxes and use them to fill the query

I am sure this is not complete enough for you so ask for what your missing, I hope it gets you going.

Regards

 

[QuantumCat]

Thans for the reply

Yep you make a query for your report
Simple create the query in design view, save it and select it as your report datasource

I don't get it, Query to me is something like question. Making the Query just doens't do what I want it to do. I want to enter the report name and get only the data connected to that report name.
What I get with query is the result, not the input interface for the user.
(I guess my interpretation of "query" is based on the English dictionary, not on how Access uses it)

Sorry wasn't clear on this one, the idea is that you fill the report is the centre or the interface, e.g. I call a report from query and I meant:
by saving the report , save reportentries to a new table

No, make a seperate form with textboxes and use them to fill the query

This one I don't get

I am sure this is not complete enough for you so ask for what your missing, I hope it gets you going.

Regards

I begin to understand the relations between the text boxes -report or form,

But I still so not get how I (re) call the data. I made a query so I can couple that to a form or a report (preferably a report)
this form (or reports) needs to have the interface to enter only the report number and to retrieve all data from the data base filling the reportitems,
subsequently if the reportnumer is new the user should be able to enter the data in the form, then print the form and store the data in the form as a new record/database entry

Maybe I'm thinking in gthe wrong direction (I'm a VB-guy, so my thoughts may be to programmer-styled) and maybe you have given the anwers but as I am a noob, please be clear and abundant

QC

 

[QuantumCat]

Having said this, I am playing around with the textboxes and such and I can assign query entries to it.
Are they linked? I mean : if I enter the report number (I assigns query entry [=column 1] to it) , will all the other textboxes be filled out if I have assigned to them the other query items corresponding with the report number ?

And more importantly, how do I enter that "report number";
I call it query since it has database action to it: find and retrieve items linked to the item entered, but basically it's an improved search that doens't just return "repor" found, but also the other data corresponding with that report

 

[namliam]

I am actually at work, and thus dont have the time to be as "abundant" as you might like!

If you want to enter stuff into a table for reporting, just like in VB you create a form. This form holds textboxes that are bound to the table, thus filling the table.

Then if you want to report, first create a query (search thingy) to retrieve the rows you want to report. If you have a reportnr you can enter [Please enter report number:] exactly like that in the where clause of the designer window. The query will now popup asking you to enter it. But that is "ugly", you may just want to create a form and have the user enter stuff there (report number) then in the query you enter in the where as parameter: Forms("FormName")!ControlName the query will then pull it of the form (assuming offcourse its open)

Now build a report on above query and you are where you want to go.

Definitions:
Query: Retrieve data from a table, limited to a predefined set of records. Meanwhile, if applicable, doing stuff like Count, Sum, Avg, etc.
The dataset can be manipulated iether by "hard-" or "soft-" coded parameters.

Form: Data entry/data edit/display thing (same as in VB)

Report: Display only, mosttimes in a user friendly/readable format. DISPLAY ONLY, so no data entry.

I did some searching for you and found among others this:

http://www.fontstuff.com/access/acctut08.htm
http://www.access-programmers.co.uk...=pass+parameter

Try reading them on passing parameters to query's, it should help you along.

GL

 

[QuantumCat]

Okay

The second link doens't work by the way (search and enter "pass + parameter" gives no results either)

Anyway -I have abandoned this project. The time given was a week, I won't make that.

Still, now that I started, I need to know, just to satisfy my own curiosity:

the first link brought me closer, the only thing I didn't want to happen is visually opening the query, so that's not the direction I want to go.
On a VB forum I also posted, somebody returned me the
SELECT ... FROM ... WHERE statement, but I need a recordset for that.

How do you go about that in VBA ? Ho do I connect the open database to the recordset? In VB you declare a recordset and link a database to it e.g.:
[vb]
Sub ZoekRecords()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim strSQL As String


Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset

MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Secuity Info=False;Data Source=K:\Persoonlijke werkmappen\jaimy\ACCESS\test.mdb;"

DoCmd.OpenTable ("WPS Invoer")

strSQL = ("SELECT [WPS invoer.WPS naam], [WPS invoer.Lasnorm], [WPS invoer.Lasproces 1], [WPS invoer.Lasproces 2]," _
& " [WPS invoer.Lasproces 2], [WPS invoer.Lasproces 3," & "[WPS invoer.Lasproces 4], [WPS invoer.Lasproces 5] ," _
& " [WPS invoer.Basismateriaal A], " & " WPS invoer.Basismateriaal B]FROM [WPS Invoer]" & "WHERE [WPS Invoer.WPS naam] = '" & Invoer & "'")

MyRecSet.Open strSQL, MyConn, adOpenStatic, adLockReadOnly, adCmdText

End Sub
[/vb]

Since this is VB code expecting an external database, this code doesnt work(I assume that I need to connect it to something like activedatabase or at least one of the tables within the database)

QC

 

[namliam]

Hey duthy dutch dutch... I didnt even notice

You can do exactly the same in a database, except then you cannot use it for a report.

Are you in the same db? if so simply create a new query, go to sql view and paste your SQL in. Save the query, run the report on the query.... That is all there is to it....

Regardz

 

[QuantumCat]

Hey duthy dutch dutch... I didnt even notice

me neither

You can do exactly the same in a database, except then you cannot use it for a report.

this I don't quite see; though I heard reports are very limited things, so I need a form first (with the report layout, to keep things unified)

I can create command button that will start the code given above.
It's the code that fails me so far.

Are you in the same db? if so simply create a new query, go to sql view and paste your SQL in. Save the query, run the report on the query.... That is all there is to it....

Regardz

Yes, I'[m trying VBA for a change

 

[namliam]

Yes reports are very limited things ... Output only, but they are much better at looking "nice" than a printed form would look.

But you can do a form with a print button on it, the form would be the data entry part. The print button would then simply print the report.

Regardz