Convert Access Query to VB 1

[MrMajik]

Is there a wizzard or some automated method to convert an existing query to VBA code?

Thank you.

My parents didn't want to move to Florida, but they turned sixty and that's the law.

 

[psemianonymous]

If this helps, you can click on the SQL view of a query and that is effectively the same as using the query itself. Some query features (such as parameter queries, field formatting, etc.) are unique to queries and are un-transferrable directly. Unless someone has written a program to do so.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[MrMajik]

foolio12; Thank you for the reply.

That helps and will most likely become useful in the future.

I guess what I am looking for is a way to create a query in VB and run it so it gets the same results as a pre-defined query.

Thank you.

My parents didn't want to move to Florida, but they turned sixty and that's the law.

 

[jfgambit]

The following is code that you can add to c command button to run a query (You need to change the names to fit your database):

Dim dbNm As Database

Set dbNm = CurrentDb()

dbNm.Execute = "SELECT tblTableName.* " & _
&quot;FROM tblTableName&quot; <---this is the SQL from your query that foolio mentioned...


HTH




&quot;I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?&quot;

 

[MrMajik]

Hi jfgambit; Thank you for the reply.

I tried your example and added some things from the SQL statement and ended up with this:

dbNm.Execute = &quot;SELECT tblAgency.AgencyId, tblAgency.FullAgencyName, tblAssets.AssetTag, tblAssets.AssetType, tblAssets.AssetMake&quot; & &quot;FROM tblAgency INNER JOIN tblAssets ON tblAgency.AgencyId=tblAssets.AgencyId&quot;

and got this error: User-defined type not defined.

and highlights this:

dim dbNm As Database

Is there something missing from the declaration part of this?

Thank you.

My parents didn't want to move to Florida, but they turned sixty and that's the law.

 

[jfgambit]

What version of Access are you running?

You probably need to add the Reference to the Microsoft DAO object library.



&quot;I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?&quot;

 

[MrMajik]

Hi jfgambit;

I am running MS Office XP Pro on Windows XP. I have also developed a part of this db on a Windows 2k machine running MS Office Pro 2k and it runs.

I added the Microsoft DAO 3.6 Object Library and that corrected the above error message. However, it now generates the following error:

Arguement not optional

on this part of the string:

dbNm.Execute =

Do you know what it takes to correct this?

Thank you.

My parents didn't want to move to Florida, but they turned sixty and that's the law.

 

[ShawnR]

This is a better way to do this. I use it all the time. Just have your record source equal to a string. For example.

Me.SubFormName.Form.RecordSource = &quot;qCallAll&quot;

This calls up a specific save query file or literally just paste in between quotes the SQL mentioned above. You can also string together many items to build you SQL based upon other combo boxe values etc. I use it alot to dynamically change a combo and list boxes based upon some condition(s) i set threw other controls.

I hope this helps. I have not used the above method.

 

[jfgambit]

Add the Microsoft Visual Basic for Aplpication Extensibility reference.



&quot;I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?&quot;

 

[psemianonymous]

The proper usage is:

dbNm.Execute(strSQL)

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[MrMajik]

A part of the trouble I am having with this is the Execute method is valid only for action queries. I am trying to build a select query that will display a table with the results.

Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
--Albert Einstein

 

[MrMajik]

Sorry about the double posting on this...

A part of the trouble I am having with this is the Execute method is valid only for action queries. I am trying to build a select query that will display a table with the results.

How do I run a query that will return the following in a table:

&quot;SELECT tblAgency.AgencyId, tblAgency.FullAgencyName FROM tblAgency;&quot;

Thank you.

Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
--Albert Einstein

 

[psemianonymous]

Aha. In that case,

Set rs = dbnm.OpenRecordset("SELECT tblAgency.AgencyId, tblAgency.FullAgencyName FROM tblAgency;")

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[MrMajik]

Hi foolio12;

I think we are getting closer...

Here is what I have so far:

Dim dbNm As Database
Set dbNm = CurrentDb()
Set rs = dbNm.OpenRecordset(&quot;SELECT tblAgency.AgencyId, tblAgency.FullAgencyName FROM tblAgency;&quot

When this runs nothing visual happens but I can tell it created the table because the hard drive lights up for a moment.

Can you see what is missing?

Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
--Albert Einstein

 

[psemianonymous]

Ahh, sorry, display the results. This is the wrong way to go about displaying the results.


Recordsets have no visual counterpart, so ... you're going to have to take an entirely new angle. Consider either:
1. A make-table query which creates a table that you can then open in standard table view
2. Changing a QueryDef programmatically (the .SQL property) and opening it in standard query view
3. Making the SQL the data source to some sort of report or form..



No no no. What are you after? what are you trying to do? i just read your first post, and it's entirely different from your last. You tell me, then I'll try and help more.

 

[MrMajik]

Hi follio12;

I just read the original post and it does not explain much of what I am trying to do. I will try to be more descriptive this time...

Depending on what a user selects on a form I want to be able to generate a query and have the results displayed in a table.

When working properly the example I posted will display the Agency ID the user selected from a combo box and the full agency name. The combo box is filled with items from the AgencyID field from the tblAgency table.

In a different wording here is what I am trying to do...

I want to display fields from a table by running a query. I want to do this using VB code.

There is no way I can know what the user has selected and too many combinations to try to design querys from using the query wizard.


A little more background:
This is a one-to-many relationship design.
One = tblAgency
Many = tblAssets

To keep this simple I only need to view two things from the tblAgency table; the Agency ID and their full name. I think I can get the rest going once I have this piece of it.

However, the main thing from the child tblAssets table is the AssetTag field that will be included in almost every query that is ran so if you can show me how to include a field from the child table that would be helpful.

Thank you for sticking with this.

Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
--Albert Einstein

 

[psemianonymous]

Aha! I think I've got what you need. You want a multi-column combobox. You want it to store the Agency ID but you want to be able to select via the Agency name? If so, there are a few steps (none involving VBA):

1. Find combobox in the form's design view, open the properties box.
2. For the &quot;Row Source&quot;, make a SQL statement that includes both fields. Aha! Here it is:

SELECT tblAgency.AgencyId, tblAgency.FullAgencyName FROM tblAgency

3. &quot;Bound Column&quot; is set to 1
4. Now click on the Format tab
5. &quot;Column Count&quot; = 2
6. &quot;Column Widths&quot; = &quot; 1&quot;;1&quot; &quot; - adjust as necessary.
7. &quot;List Width&quot; = 2&quot; - adjust as necessary. Recommend at least as big as the combined Column Widths


Now you should see what you've been wanting to see.



Also, as a side note, you should look up the DLookup function--it basically runs a quick query and returns a single value--good for things such as you originally wanted - but the combobox solution is better.


If I haven't nailed the question as you desired, let me know--I'm still here.


Pete

 

[MrMajik]

Hi Pete;

At this rate I am inviting you over for a nice holiday dinner

The agency name and agency ID are created when the parent record is first created. The query would include them for location identification purposes only. What I am going to use this for is to show that Agency ABC (American Bootie Corp.) has x amount of computers, y amount are Dells or z amount are Gateways.

However, computers are only one option that a user may want to run a query on. I have a drop down combo box that is gets populated with every item type in the database at that moment. So the drop-down combo boxes get repopulated each time they are opened. Other items could be file servers, file server names, cameras, scanners, printers, etc and this list is always changing.

So my query will need to be dynamic depending on the choices made in the combo boxes by the user. If the user wants to see how many 1 Gig PCs ABC has then they would choose

Agency: ABC
Asset: Computer
Attribute: CpuSpeed

from the three pull down combo boxes and a query will fetch this for them and display it in a query table.

Does this help clear up what I am trying to do?

Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
--Albert Einstein

 

[psemianonymous]

Okay. I have it now. I really have it.


First, before we get to the programming, the setup:

1. Agency control, which I will name ctlAgency
2. Asset control, which I will name ctlAsset
3. Attribute control, which I will name ctlAttribute

Your control names will vary.

4. What you know of as &quot;the form&quot; will now be placed in the &quot;Form Header&quot; section of the form. All of it. All of the comboboxes, etc. Move them to the header. This will break all events related to your controls--there is no help for that. This makes way for...
5. The detail part of your form. This you will set to &quot;Datasheet&quot;, thus looking the most like a table, and the simplest way to list the data. The other option is Continuous, but that's for later, if you want to do some formatting. This is your embodiment of the &quot;visual result&quot; of your querying.
6. Anything you want to appear below all the listed items, put in the footer. But that's a matter of taste; I leave it up to you.
7. The form's current recordsource is empty/blank. We will fill this in later.


Now, for the pseudocode:

Stage 1: fill in information on comboboxes
Stage 2: user clicks &quot;Search&quot; -- run search ((***))
Stage 3: update form to list search results

((***)) - I'd recommend that you only run the query when the user chooses to.

Stage 1 requires no coding.

Stage 2: On the Click() event of the search button, dynamically create your SQL string based on what is currently in your comboboxes.

Stage 3: After you have assembled the SQL, do the following line:

Me.RecordSource = strSQL  'or whatever the name of your string is

This hasn't given you the exact answer--but I can't give you the exact answer without a lot more detail. But I believe that is what you're after. To visually represent your newly-assembled SQL statement, assign it to the form's recordsource and it will automatically requery and display the information.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[MrMajik]

Hi Pete;

I have this coded in a different db and it may work. I will let you know how it goes.

Thank you!

Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy.
--Albert Einstein