Cleaner Code with DoCmd.RunSQL Insert into

[mduplantis]

Fantastic information. I have been going back and forth between using a unbound form to collect the data and shoot it back to the back end, or using a split database. Although I do like to code, I don't necessarily want to create more work than is needed. 95% of what I will be doing is collecting data, not querying. If I understand all my reading up this point, I fully develop the database and then split and distribute it to each PC. This will allow each user to input data and not be "locked" out by links to the back end.

Thanks for all the suggestions and the sample database. The sample database is exactly what I was looking for if I choose to code the unbound form. I am going to continue to read and study, but I think I am leaning towards using a bound form and then split and distribute the database.

I will follow up with the links you provide.

Again, thanks to both of you for your time and comments.

Mike

[rpeare]

I took a much closer look at the example and I think I understand most of it. One follow up questions would be to asked about updating multiple tables?
...snip...
For this project, I will only be collecting data in a form, but it does go into multiple tables.

You would have to be far more specific about why you need to update multiple tables with the same information, or is it different information to different tables but you want one data entry form. Without knowing the specific reason it's hard to give you guidance.

[mduplantis]

You would have to be far more specific about why you need to update multiple tables with the same information, or is it different information to different tables but you want one data entry form. Without knowing the specific reason it's hard to give you guidance.

My apologies, I should have been more clear. Yes, I would have one form that would collect different information that would go to several different tables. My primary key is the incident number (unique identifier for the exposure) and 4 other tables are linked in a one to many relationship based upon the incident number.

I hope that helps.

Thanks

[rpeare]

If you are going to use bound forms as you indicated in post #16, you would use a form/subform arrangement with the proper linking criteria (FK or Foreign Key on the 'child' table linking to the PK or Primary Key on the 'master' table) the you don't need to build a sql statement.

[Missinglinq]

Here's a simplified example (for a customer order database...a typical use for this kind of thing) where customers can place multiple orders; it might make the Main Form/Subform thing a little clearer:

CustomerTable
CustomerID 'Primary Key
CustomerFirstName
CustomerLastName
CustomerHomePhone
CustomerStreetAddress
CustomerCity
CustomerPostalCode
...and so forth

OrdersTable
OrdersID 'Primary Key
CustomerID 'Foreign Key
OrderDate
MethodOfPayment
...and so forth

Notice that the Tables 'cascade.' The Primary Key of the first Table becomes the Foreign Key of the second Table. Since all of your subordinate Tables are in a one-to-many relationship with the first Table, you'd simply repeat this in the same manner as the OrdersTable in the example for each additional Table.

To display all of these in one Form and to be able to enter New Records or Edit Existing Records in all of these Tables:

• The Main Form would be based on the CustomerTable
• The first Subform would be based on the OrdersTable and linked to the Main Form by the CustomerID
• Each additional Subform would be based on their own Table and linked to the Main Form by the CustomerID
  

Linq ;0)>

[mduplantis]

I am familiar with forms and sub-forms as I do work with a few in the past. I was under the wrong impression that I could create a form, without using a subform, to collect data into separate tables. I will need to readjust the plan for my form.

I was planning on doing a little more work to my tables and relationships before posting another questions, but I'll attach to give you an idea of what I am working on. The from will be used to collect infectious disease exposures for fire fighters. Because one incident can create multiple exposures, I created a many to many relationship (with linked table) between the incident and exposures tables. I am not sure I have the Office use table correct. I am thinking I need to move it towards the incident table as the results will be the same for all exposures.

I admit, I am still pretty new to this and still get turned around easily. I'll need to work on it a bit more to make sure My auto-numbers are populating the many side of my relationships with the correct numbers.

Also after careful consideration, I think I am going to take a conservative approach and use a split database. I don't need to add any more complexity than is needed. Using the split will allow me to code some more functionality without having the core project rely on my code.

Thank you,

[rpeare]

You can enter data on two tables with a single form (without involving a subform) one of three ways.

1. Bind the 'main' form to your 'main' table, and have unbound controls for your 'sub' table
2. Bind you 'sub' table to your 'main' form, and have unbound controls for your 'main' table
3. Create an entirely unbound form.

I gave you code to cycle through controls and create a sql statement to add them to a table. you would just need a few new functions to handle adding/updating/populating data based on the selection on another part of the form. I usually do this by adding a string like 'Sub_' in the TAG property of my data entry fields. Then as I'm cycling through controls to add items to the sub table I look only for controls that start with the string 'sub_' in the TAG property.

Now as to the relationship between your tables

If you can have multiple people on a particular incident and you can have multiple disease exposures on a particular instance you can also:
A. Treat every person as though they were exposed to ALL the diseases
OR
B. Each person would be exposed to any number of the diseases.

So For instance on Incident A there were 5 infectious diseases present. There were also 3 people present at the incident.
If you opt for A (above) you wouldn't need to create a cross reference between the diseases present and the people present unless you were going to track how each person was treated because of being involved in the incident.

If you opt for B you would need some method of data entry to say Person 1 was exposed to Disease 1, 3 and 5, Person 2 was exposed to disease 1 and 2 and Person 3 was exposed to disease 3, 4 and 5.

I do not use relationships when I build databases, so I'm not terribly worried about your relationships, I think more about what is the actual practice of your process.

Try taking a step back and describe your actual process in plain english, don't attempt to connect it to database language. Explain it like you would to someone who has no concept of what you do on a day to day basis surrounding this issue (because we don't).

[mduplantis]

That is excellent advice. I did not think about using a hybrid of the two. I will take the time this weekend to step through it as you suggested.

I get the looping and the code, but I'll have to look at the tags and other functions in more detail.

Thanks for all the comments!

[mduplantis]

Hi,

I was able to create a temporary form, label and adjust the code you provided (attached my 1st attempt). I still have some work to do with input mask(s) to make sure what I am collecting will insert into my table correctly. For this and many of my projects, I only collect and insert data (no query, append, ect). I think I am gathering a good understanding how the code is working, but I am still uncertain how to add data to another table(s). I think I will need to collect the new record number that is created with my 1st INSERT statement, assign it to a variable and then use the variable to populate the second table in a separate INSERT statement. This might also require me to identify (maybe the _sub you mentioned) the different fields that will go into the different table.

The overall function was exactly what I was looking for. If I were using the functions, I would create separate INSERT statements and manually list all the fields.

Thanks for the help. I learned quite a bit about tags and using your function.

Mike

[rpeare]

1. The form isn't working at all, you've removed some of the functions from your global functions list but still calling them in your code (requerylist), in essence it's adding the record but you have no list to requery and calling a function that no longer exists
2. Your providers should be referenced by a FK (foreign key) to a PROVIDER table so the user can just select a provider and the provider information is filled in on any reports/queries
3. I didn't check fully but if you didn't rewrite any of the functions I gave you in the unbound data entry example database your labeling is not what the code is expecting (i.e. your text box with the primary key should be named ID, the caption for that text box should be ID_Caption, similarly any required field should be the name of the field followed by _caption... IncidentDate_Caption, OFDIDNum_Caption
4. Similarly I assume your OFDIDNum is the personnel identifier, your personnel should have their own table and you would reference them by their FK (foreign key) not on the incident table, but on the incident roster
5. Your incident needs to have a 'sub' table listing the personnel that were on the run.

I'll repost your database after a little work in the morning

[mduplantis]

Yes, that is all correct. I should have explained what I was doing. I was stepping through the code just to see how the SQL statement was working. I thought I had went through and labeled correctly, but I will go through again. The caption on the mandatory fields was a little confusing, but I will take another swing at it again tonight reintroducing the functionality (global functions).

My first hurdle was seeing how the SQL statement was being built in the function. I am in class all day, but I'll spend several hours tonight improving my first attempt.

Thank you for the help.

[rpeare]

Working IDCO.zip

Your sample database modified
Note I added some functions for handling 'sub' table data entry and some fairly extensive notes in the GLOBALFUNCTIONS module to indicate how to set things up.

[mduplantis]

Holy Moly! That' is one heck of a sample!!!! I am starting to look at it tonight, but I will have to spend some time over the weekend before I can comment back on what you did. Thank you so much for the notes and example form. It will help me understand everything so much better.

Thank you!

[mduplantis]

Hello,

I was able to spend a few hours going through your sample code and I think I have a much better understanding. The form is broken up into segments that relates to the table being updated. The CRUD buttons call the Global functions based upon the tags. The list boxes are used to display the data and have click events to requery based up on the values. The new tab will essentially be the same set up, except using the sub3 name to identify its location. In it's simplest terms, it collects the data from the form, insert/updates the table and displays in the list box. Correct?

The last thing I am looking at is the difference between how you INSERT into the incident table, but UPDATE into the others.


sSQL = "INSERT INTO " & sTable & "


sSQL = "UPDATE " & R


I think I know why you do it, but I am looking closer on how you do it.

Thanks!

[rpeare]

First part: not the way I would have phrased it but essentially yes, keep in mind the TAB your SUB3 stuff appears on should be called SUB3 as well just to keep things clear. I think the code I gave you might break down if there is a space or an underscore in the tab name but I'm not 100% sure.

Second part: are you looking for an explanation or just describing your progress