How do I populate fields on a Query using SQL? 1

[Toga]

I usually use the Access Query's but want to learn how to use SQL in my code. In the Form Open section, I've retrieved my records via SQL. (I can do a break and see that the data was retrieved) How in the world do I get that data to populate the fields I have on my form? Also, where should I be writing my SQL code? The stupid instructions show examples of the code but they never seem to tell you how to use or where you should be putting your code!

I must be really stupid or something.....because I don't see anyone asking this anywhere! What am I missing.

 

[rafe]

The open learning process can be a pain. I’m only a step or two ahead on the learning curve.

You can do either:

1. Attach the form to a query directly at design time by putting clicking on the query builder (the “…” just to the right) on the form’s data page’s “Record Source” control. Then you:


a.Drag a field on the form using the form’s “Field List” control, which will automatically create the correct control for the form & make it attached the corresponding query field.


b.When I need to do something in a nonstandard way I put an unattached control onto the form & then attach it using the control’s “Control Source” Property. The fields will be on a drop down list using the “down arrow” on the right. Leaving a control unattached can be done but it’ll sometimes lead to strange behavior.

2. You can also attach a form or a control (I don’t do this with sub-forms but usually things “list boxes” then it’s called a “Row Source”) to a query at run time using VBA code. First you build the query using the query builder. You then put go into “SQL” view. Copy the entire query an then paste it into your VBA code, of course you’ll have to format it as a string with all of those quotes & ampersands etc. and put in your string formatted run-time variables. Something like:
Me.MyListBox.RowSource = MyQueryString ‘Note RowSource vs. RecordSource here.
Me.MyListBox.Requery ‘This will actually DO the new query

Hope this helps.

 

[Toga]

Thanks for the response but I have no idea what you just said!!!!!

 

[rafe]

OK then, ignore what I just said and let’s stick to the easiest example!

Use the form design wizard to do this. You just click on “Forms” in your DB window & then click on “Create form by using wizard.” It’s pretty self-explanatory & it should walk you thru the process.

It does an OK job for a first approximation.

Sorry for the confusion.

 

[Toga]

Ahhhhhh Connecting to a data source that way I am familiar with. What I want to know how to do is to copy that SQL code that you generate graphically in form view and imbed it into my code......code that I write. When I've tried, I can do set a break and verify that the SQL worked as data will be attached to the variables in the SQL code when I do a debug.print....but how do I go from there and populate the fields in my form?

 

[rafe]

Maybe I’m not the best to explain, so I won’t get too insulted if you want to try another person.

We seem to have been talking past each other so we’d better get specific. You probably already know most of this, but I want to make sure we’re on the same footing.

When you generate a form using the wizard you’re primarily generating objects with a little SQL in their object properties. Keep this in mind: Objects sometimes use SQL (as strings) in some of their properties & you can place the SQL string into the object’s property in VBA code. Which is what you’re trying to do. Also keep in mind that Access VBA is primarily event driven. So you write VBA code that responds to user events like a click or loading a form.

Example 1: (I’m doing a mock-up as I write.) This is the easiest, I’m going to populate a control using VBA. If you stick with me on this we’ll go on to a Form example.

Housekeeping:

I created a table “Table1” with two fields Table1![Name] which is the primary field and Table1![Category], notice the funky notation (don’t worry about this now but it’s one way referring to a recordset object’s fields). I entered data into this table as follows:
Name Category
Jon Drone
Rafe Drone
Toga Boss
Andrea Drone
Carol Boss
Bob Boss

Next I created a form (w/o the wizard). With a ListBox and two Buttons. Still in design view I called up the properties tab sheet (on the view menu). I then went to the properties sheet & changed the names of these objects by clicking on the object, then clicking on the “Other” tab of the properties page & then filling the Name properties to “DroneButton”, “BossButton”, & CatListBox. I also changed the Caption property of the two buttons under the “Format” tab.

Now to VBA.

When you click the DroneButton or BossButton I’m going to use simple SQL to change the CatListBox display.

On the property sheet for the DroneButton I clicked on the “Event” tab and just to the right of the “On Click” property I click on the “…” and choose “Code Builder.” In the DroneButton_Click procedure you change properties of the CatListBox as follows with the following VBA.

Private Sub DroneButton_Click()
Dim SQLstring As String 'Built using Access's Query Builder
‘Use Single quotes around ‘Drone’ NOT double quotes
SQLstring = "SELECT Table1.Name From Table1 WHERE (((Table1.Category)='Drone'));"
Me.CatListBox.RowSource = SQLstring 'Change the population source
Me.CatListBox.Requery 'Make the change real
End Sub

For the Boss button I did almost the same…

Private Sub DroneButton_Click()
Dim SQLstring As String 'Built using Access's Query Builder
‘Use Single quotes around ‘Boss’ NOT double quotes
SQLstring = "SELECT Table1.Name From Table1 WHERE (((Table1.Category)=’Boss’));"
Me.CatListBox.RowSource = SQLstring 'Change population source by changing the object’s property
Me.CatListBox.Requery 'Make the change real by performing object’s method
End Sub

When you click on the DroneButton or BossButton the CatListBox is “populated” by either drones or bosses.

One further comment is that I don’t just write those SQL strings myself. I used Access’s “Query Builder.” After I get the query to work the way I want, I go in to the “SQL view”, it’s on the “View” menu, and copy the entire query in to the VBA code setting up the SQLstring variable, you’ll have to mess with formatting a bit. Important use single quotes around the ‘Drone’ selection criterion for the “Category” field.

Are you still with me? |-I

 

[Toga]

As scary as it sounds.....I think I'm still with you! I gave it a try and actually got it to work!

Thanks for your help getting started here. I'll play around with it a little bit trying some different things and will let you know should I need additional help!

We both know I will don't we! I just prefer to bang my head against the wall a little trying to figure it out on my own before I raise the preverbial white flag.

Again....Thanks!