VBA SQL Statement fill out a pre-made Form

[Strike_Eagle]

Hello everyone, I am having a little trouble getting a VBA SQL statement to fill out a form that is already premade.

The goal is to make the user interface with a sereis of boxes and controls to modify a record, or records, after data has been entered. So I created a sub-form entitled "Discussion Select Form" that works just fine.

When I click my "Retrieve Information" button, I want it to fill in the appropriate boxes for the dynamic VBA SQL statement that were selected. There will be blank, null, boxes because records of one type may or may not have related records. Unfortunately, my relations are correct under the Database Tools Relations, as you can have one record that could have mis-matched sub-parts

I have chunked this, and only working on the first element, 'customer service' so that I can get the form working, then I will add all of the other variables.

A datasheet to display all of the records that the statement provides would be good, because then you could select each record for the statement. I do not know how to add this feature. There is practically no visual polish on this, as I can fix that out once I get it to work properly.

The goal is to have one form do it all and to limit the number of saved queries. I am primarily a Java developer, ametuer to that even, so I apologize for my horrific code.

Thanks everyone, as I know you are busy and have provided others and myself with awesome information to help them learn the information themselves.

 

[Strike_Eagle]

I did forget to mention I am using Access 2007 on a XP machine. Thanks for the views, but does anyone know where to point me to find out how to fill out that form?

 

[pbaldy]

It sounds like you're trying to do the type of thing done in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

I noticed your user name. Are you associated with the F-15E? My son-in-law flies them.

 

[Strike_Eagle]

It sounds like you're trying to do the type of thing done in the sample db here:

I noticed your user name. Are you associated with the F-15E? My son-in-law flies them.

I will take a look at that, thank you.

As for the F-15, no, I am no longer a pilot, I am an Air Traffic Controller and the F-15 is one of my favorites though. The name came from my younger days playing Counterstrike!

 

[Strike_Eagle]

Just went through the link, I think that may be right on target, thanks a ton for the suggestion. I will be going over itwith a fine-tooth comb to learn it better to apply to my situation.


Thanks Again,
Jason

 

[pbaldy]

No problem Jason; post back if you get stuck. I'm flying tomorrow, so stay sharp!

 

[Strike_Eagle]

I did get my SQL statement working correctly, now I just need to work on filling out the main form. Thank you again for you help! I hope your flight went well.

Jason

 

[pbaldy]

Glad to hear it. Though the last leg of our trip was pretty bumpy, we arrived safely. The smaller commuter jets bounce around a lot. My arm is still sore from my wife's grip on it!

 

[Strike_Eagle]

LOL My wife is terrified of flying, she has taken a total of three flights in her life and I was on two of them with her.

I hate to ask for more help, but the truth is, I simply can't seem to find my answers after reading two books on the subject. I know I have to be missing something simple, but not sure what.

The SQL statement that is dynamically formatted, can I get the results to populate in the main form with all of the additional records it returns put into a datasheet view at the bottom of the form? I know I am stretching some big limits here, but that is what the boss man wants. I am trying to avoid creating almost one thousand possible SQL statements (960 to be exact) and have a different form view to edit entries in each case.

Your website was excellent, and touched on the subject, but the problem is that I have one-to-one relations explicitly in my databse.

 

[Strike_Eagle]

I should reiterate, fidning out where the information is is still my priority. Simply asking for the answer to where that is, I would never presume to ask for the answer to be handed to me.

If, in fact, you have and I am just not getting it, then I might need some help with explanations on the material and would be glad to take suggestions where I can get that, as I know your time is limited and a nagging forum-follower is not what you need or want.

Again, thank you for any and all suggestions.

 

[Strike_Eagle]

I think, instead, I am going to use the expression builder part of discussion select form for each part and have multiple forms and queries. The code is alot simpler this way.

Bascially I am creating an invisible box that will update with the various selections made for personnel, then create my query in the Design View with an expression that will pull the information dynamically that way. My retrieve button will simply pull up a query based on the PM select.

Then, as I grow the database to the more intricate items, I can expand the "web" of forms and queries to a more manageble number. I think it will make it a lot more readable too. Still not sure how I will populate all of my views the way I want, but I am thinking a split form will accomplish this just fine.

Again, thank you for your time and patience.

Jason