Best way to append data to a table

[dwdjango]

Hi there,

I'm currently at a wall as to how I would go about appending data to a table that has been entered by a user on a form.
My initial idea to tackle this was to create a number of text boxes as a method of user input which would all then transfer to a table but this doesn't have much longevity to it(if the database requires additional columns to be added, ect)
The setup at the moment is two tables, a main table, and a temporary table(which is where I intend to first store the user input, this is so that the user can view what they have entered and make any necessary changes(undo))

I'm wondering if there are any easier ways to go about this such as, a msgbox appears and asks the user to enter each individual column data for a row. This doesn't sound like the most efficient way but efficiency isn't a priority right now and is something I'll look at later.

In my head ideally I'd like it so that a user can input data into a number of text boxes, a button has an onclick event that will append all entered values to the temporary table, this is then relayed(I'm assuming through requery?) back to the user as a way of checking before really adding it to the main database, and then if they're happy there is another button which is then enabled so they can add it to the main database.

If I could get some second opinions on the matter that would be great, and of course any example code would be brilliant!

Thanks in advance.

 

[rodmc]

use the form wizard or open a blank form and then select the table you want to add data to and set this as the record source, then just use the "add existing fields" feature, if you want to ask the user if they wish to save the data before comitting to the table you could use the some VBA to ask if the user wants to save the data to the table with something like this

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

If MsgBox("Are you sure you want to save the record?", vbOKCancel) = vbOK Then 

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End If
End Sub

 

[dwdjango]

Hi there,

Thanks for the response, I came across a way I seem to be more to what I'm after, I've attached the vba code I used below to append the data.

Dim Response As VbMsgBoxResult
Response = MsgBox("Append to temporary table?", vbQuestion + vbYesNo)
If Response = vbNo Then
Exit Sub
Else
CurrentDb.Execute "INSERT INTO tbxxxxTemp (DxxxxxxxTemp, TxxxxxTemp, MxxxxxxTemp, VxxxxxxxTemp, LxxxxxxxxTemp) VALUES ('" & txtxxxxxxx.Value & "', '" & txtxxxxxxx.Value & "', '" & txtxxxxxxxx& "', '" & txtVxxxxxxxx & "', '" & txtxxxxxxxx & "')", dbFailOnError
Me.subxxxxxContainer.Requery
End If

My structure goes like this:

Temporary Table -> Query for certain columns -> datasheet form with record source -> user input form using a subform to show datasheetform -> temporary table entry with cmdbutton to append data -> button to import from temporary table to main table if user is satisfied.

 

[Pat Hartman]

Do you really need to go through all this? Why can't you just update the final table directly? Sometimes this staging table technique is useful if the user needs to enter multiple rows and only when the set of data is complete do you want to validate it and then append it to the permanent tables. But, for a single record, it is rare to go through this proces.s

 

[way2bord]

Hi there,

Thanks for the response, I came across a way I seem to be more to what I'm after, I've attached the vba code I used below to append the data.


My structure goes like this:

Temporary Table -> Query for certain columns -> datasheet form with record source -> user input form using a subform to show datasheetform -> temporary table entry with cmdbutton to append data -> button to import from temporary table to main table if user is satisfied.

If you need to work with a preliminary "temp" table to store end-user input before "approving" data into the main table -- a few things to remember:

- The temp table needs to be on your front end, while the main table needs to be on your back-end. Otherwise, multiple users are going to be messing up each other's "temp" data.

- Whenever you run an import - you need to clear and/or delete the temp table -- otherwise your current code will continuously upload duplicate temp table data.

Good luck!

 

[dwdjango]

Thanks for your responses.

Do you really need to go through all this? Why can't you just update the final table directly? Sometimes this staging table technique is useful if the user needs to enter multiple rows and only when the set of data is complete do you want to validate it and then append it to the permanent tables. But, for a single record, it is rare to go through this proces.s

To be honest I don't really know if I have to go through the entire process, it's just I saw it as an opportunity for the user to be able to have a way of approving what they've entered. Other than the first Temporary column, the other pieces do not necessarily have information to be added and are seen more as an 'optional' form of input.

If you need to work with a preliminary "temp" table to store end-user input before "approving" data into the main table -- a few things to remember:

- The temp table needs to be on your front end, while the main table needs to be on your back-end. Otherwise, multiple users are going to be messing up each other's "temp" data.

- Whenever you run an import - you need to clear and/or delete the temp table -- otherwise your current code will continuously upload duplicate temp table data.

Good luck!

This is my first database that I'm working on and your post was very insightful. As to the front end/back end, can you elaborate as to what this means? There'll only be one user using the DB as I'm creating it for their specific needs.
I'm assuming that once I've appended to the temporary table it's just a matter of using DELETE after the user has approved and has clicked to append to the main table?

Just in case anyone reading has any further ideas as to how I may go about this feel free to post, I am completely new to this stuff!
Thank you very much.

 

[Pat Hartman]

If this is your first database, I would suggest the KISS method. Don't do ANYTHING you don't actually need to do. The best use of your time is to learn how to use form events correctly so you can control IF and WHEN data gets saved. The FORM's BeforeUpdate event is the LAST event that runs before data is actually saved so this event is a good choice for "do you want to save" messages since you can cancel the event to prevent the save at this point.

 

[dwdjango]

Hah yes the KISS methodology. On the matter of not doing anything, I'm not required to do any validation at all, but it seems stupid and careless not to include it, especially when working with the input of data deleting existing records, you're right. I've posted below how I ended up tackling it.

For example
I use a 'Create Temporary' button's onClick event which is basically one big IF statement with a couple of nested IFs.

I start the IF checking to see if the required text boxes are Null, if they're null then a nested IF with an information prompt is shown informing the user that they must complete all text boxes and using vbOKonly so forcing to click OK means to exit the sub routine.
Otherwise if all required fields have data, then a nested if where again a msgbox is shown asking if the user wants to append to the temporary table, if they choose no then again the sub routine is exit, else using DoCmd.RunSQL (variable i have associated to sql code) to append to the table.

That was for my appending to table, for deleting I simply added an autonumber and then used a nested SQL to SELECT the max value(last added) and delete where a value is equal to this (last added).

Thanks for the help throughout in my post and I hope anyone viewing with the same problem is too.

 

[Pat Hartman]

If this is your first database and you have no trouble writing code or SQL, then you must have programmed in some other environment where you had to do everything yourself. Access is a Rapid Application Development environment and you can go with the flow and take advantage of that or you can code everything yourself. Much of what you are doing with code can be handled by validation rules on the table. Of course, the error messages can get cryptic so you may still want to validate yourself or you can trap the errors in the Error event but the point is, Access won't allow bad data to be saved so as long as you are talking about a single record, there is no need for a staging table and the code and queries necessary to move from one to the other. This is bad enough in Add mode, have you thought about how it will work in Update mode? Are you going to first copy the existing record?

As long as you realize that the Form's BeforeUpdate is the funnel through which EVERY SINGLE ADD/UPDATE must pass, you can control what gets saved and what doesn't. Deletes, you have to handle either by preventing them by setting AllowDeletions to No or with the BeforeDeleteConfirm event.

 

[missinglinq]

I feel strongly about this and have to reiterate much of what Pat has already told you as well as add some thoughts.

I cannot think of a worse way to learn about Access than using Unbound Forms! Using Unbound Forms does away with the main reason that developers choose to use Access, which is to take advantage of it ability to facilitate the RAD (Rapid Application Development) of Databases.

With Bound Forms, Access does the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for just about everything, even the most mundane tasks. Several developers I know, experienced in Visual Basic database development, as well as Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms.

In short, there is very little that can be done using Unbound Forms that cannot be accomplished with Bound Forms, and usually done with less work!

Anyone who insists on using Unbound Forms, would be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

It should be added that there are a few very specialized reasons for using Unbound Forms in Access, for general data entry of Records, but they are very specialized, and situations that a newbie is very unlikely to come upon. I've been writing Access apps for well over a decade now, and have never had the need for using Unbound Forms for general data entry.

I applaud you efforts to learn Access, but you should first learn to use it as it is intended to be used, then you can move on to modifying its behavior, if the occasion arises.

Linq ;0)>