Access 2007 Subform acting as clipboard, not adding new row to subform table

[mdewell]

I am using Access 2007. I have a form with fields that I want to copy to a subform. I nave an ADD button that can sopy the data to the subform, so the subform is acting as a clipboard. I can get it to add a row, but not add a new row to subform table after doing one entry into the subform.

Here is some code I have tried:

    With Me.GiftListSubform
        .Form.SetFocus
        .Form![GiftQuantity].SetFocus
        RunCommand acCmdRecordsGoToNew
        .Form!GiftQuantity = Me.GiftQuantity
        .Form!GiftItemReceived = Me.GiftItemReceived
        .Form!TitleRank = Me.TitleRank
        .Form!FirstName = Me.FirstName
        .Form!LastName = Me.LastName
        .Form!GiftDate = Me.GiftDate
        .Update
    End With

It doesn't like the .Update with this one.

    Forms![GiftRecipientForm]![GiftListSubform]!GiftQuantity = Me.GiftQuantity
    Forms![GiftRecipientForm]![GiftListSubform]!GiftItemReceived = Me.GiftItemReceived
    Forms![GiftRecipientForm]![GiftListSubform]!TitleRank = Me.TitleRank
    Forms![GiftRecipientForm]![GiftListSubform]!FirstName = Me.FirstName
    Forms![GiftRecipientForm]![GiftListSubform]!LastName = Me.LastName
    Forms![GiftRecipientForm]![GiftListSubform]!GiftDate = Me.GiftDate

It copies one record here but doesn't let you add more than one record into the subform at a time.

Can there be a way to have the Add button create rows each time it is clicked with updates of textbox entries data?

[jforbes]

Using the UI to do something like this can be like a house of cards. Tricky to build and even trickery to maintain.

I would recommend creating a Method to insert records into your Sub Form/Table (GiftList), then call the Method from the Main Form/Table (GiftRecipient) . You could create a Function similar to this:

Public Function addGift(ByRef lGiftQuantity As Long, ByRef iGiftItemReceived As Integer, Byref sTitleRank As String, Byref sFirstName As String, Byref sLastName As String, Byref dGiftDate As Date) As Boolean
    
    Dim sSQL As String

    addGift = False
    
    sSQL = sSQL & "INSERT INTO GiftList ("
    sSQL = sSQL & "  GiftQuantity "
    sSQL = sSQL & ", GiftItemReceived "
    sSQL = sSQL & ", TitleRank "
    sSQL = sSQL & ", FirstName "
    sSQL = sSQL & ", LastName "
    sSQL = sSQL & ", GiftDate "
    sSQL = sSQL & ") VALUES ("
    sSQL = sSQL & "  " & lGiftQuantity & "'"
    sSQL = sSQL & ", '" & iGiftItemReceived & "'"
    sSQL = sSQL & ", '" & sTitleRank & "'"
    sSQL = sSQL & ", '" & sFirstName & "'"
    sSQL = sSQL & ", '" & sLastName & "'"
    sSQL = sSQL & ", #" & dGiftDate & "#"
    sSQL = sSQL & ")"
    
    CurrentDB.Execute sSQL, dbFailOnError + dbSeeChanges

    addGift = True
    
End Sub

Then on your Main Form, you can have your button that when clicked would call the Method kinda like this:

Private Sub btnAdd_Click()

    Dim lGiftQuantity As Long
    Dim iGiftItemReceived As Integer
    Dim sTitleRank As String
    Dim sFirstName As String
    Dim sLastName As String
    Dim dGiftDate As Date    

    lGiftQuantity = Nz(Me!GiftQuantity .Value, 0)
    iGiftItemReceived = Nz(Me!GiftItemReceived.Value , 0)
    sTitleRank = Nz(Me!TitleRank.Value , "")
    sFirstName = Nz(Me!FirstName.Value, "")
    sLastName = Nz(Me!LastName.Value, ""0)
    dGiftDate = Nz(Me!GiftDate.Value, Now())

    Call addGift(lGiftQuantity, iGiftItemReceived, sTitleRank, sFirstName, sLastName, dGiftDate)

    Me.SubForm.Requery

End Sub

The nice thing about this is that if the addGift Function is placed into a Module and made Public it can be called from any Form or Method in the Database. You could then take it further and add some business logic/validation to the Function to make sure you aren't adding duplicates or that all the necessary information is provided and jives.

[mdewell]

Thank you jforbes,

This is some awesome looking code. I'm a little more an engineer than a coder, but maybe I can learn. There seem to be some typos in what you had, but that's easy to understand. I'm guessing you weren't copying and pasting from your own test code. It all seems to work OK, except for an error when trying to run the SQL statement. Here is an example of what the sSQL variable looks like when trying to run the SQL:

"INSERT INTO GiftList (  GiftQuantity , GiftItemReceived , TitleRank , FirstName , LastName , GiftDate ) VALUES (  '1', '2', 'CTR', 'Matt', 'Dewell', #8/6/2015#)"

Can you tell what might be going wrong here?

Thanks a lot.

[mdewell]

Hello jforbes,

I think I found what the error was. A variable was defined as an integer, when the field was a string in the DB table. I got that fixed. Plus a control name was not matching, but that's not a problem now.

Here's where things get a little funny. It added it to the table fine. Yet it doesn't seem to show it in the subform. It clears out the entry from displaying in the subform as soon as the query is run. At least that's what it looks like. It at least shows up in the Table when I go to just open the table. What can I do to get the subform to display the table entries that have been added? Do I have something set wrong with my subform?

Thank You

[jforbes]

Yeah, I see some of the errors you are talking about. I just copy and pasted into the Reply Box something similar I had and then copy and pasted from you example. I'm fighting the urge to correct the typos, but I think I can resist. =)

In my experience it's lining up the datatypes seems to take up the most time and is the most error prone part of a SQL insert. Strings need to be Single Quoted, Numbers must not, and Dates need to be boxed in with # (only in Access, in SQL Server they can be Single Quoted). So my guess it needs to be more like this:

"INSERT INTO GiftList (  GiftQuantity , GiftItemReceived , TitleRank , FirstName , LastName , GiftDate ) VALUES ( [iCODE] 1, 2[/iCODE], 'CTR', 'Matt', 'Dewell', #8/6/2015#)"

I realize the orginal code wasn't right, but again, it was some copy and paste magic. Additionally, I typically use a SQL Server Backend, so I took my best shot at the Date syntax.

[jforbes]

You'll probably need to include the Primary Key of the Main Table (GiftRecipient) as part of the Insert into the Sub Table. It would be the same column that you used to link your Sub Form to your Main Form. I just guessed as to the columns you would need.

[mdewell]

OK, now this is where it gets really weird, at least for my knowledge of Access, the client wants this subform for entry of multiple lines into the main table. So the process I was thinking was to have this temporary subform/table, and then copy the lines into the main table. I guess, perhaps my better option would be to somehow create a subform that only shows the matching ID entries for the primary keys. I believe I have the primary key in a hidden field on the parent form of the subform. So, should I even use a temporary table, that I think is probably a bad idea, or somehow crate a subform that shows the entries that match those clicked by the add button? Please ask if this sounds too confusing.

Thank You

[mdewell]

To clarify a little better, each item added by a click would create a new entry in the main table, but the client wants all the same info to remain there in the parent form, as well as the list of items to add, before a final save of the record(s). Any idea of a way to do this? The big item is to have a subform to show the items being added, and then creating a new record in the main table after the save button is clicked.

Thanks.

[mdewell]

I'm trying to explain to the client about linked tables, one to many relationships. Cross your fingers. I won't hear back for a while though. Sorry if I was driving anyone to say, "What's the point in using Access, when you can't use in a relational manner?" I just think the customer isn't that familiar, but has done a little work in Access, yet is using me for the final product work.

[mdewell]

A problem I would have is converting the existing single entries table into the new table, with a relationsal field to the full gift table. Anywhere I could find out about writing a routine to do the conversion? I'm guessing it's a routine I need to write and do once.

Thank You

[jforbes]

If you are wanting a Subform (Continuous Form/Grid) to enter information, here are some things to consider:

Types of Controls

Trying to use Unbound Controls in a Datasheet (Grid) wont work because access will consider each control in a column the same control. It's odd, but it's the way it works. With unbound controls, anytime the value in a column is changed, it changes for every row in the column (only in a Datasheet)... so you'll need to use Bound Controls.

When using Bound Controls, Access will save a record when an edit is performed and then the Current Record is Changed, so clicking around in a Datasheet (Grid) and modifying data will cause the records to be saved.

Since what your Customer wants is basically a Posting process, you have two options. You could create the records for them to edit in a Temp Table and then when they Post the Changes, copy the records over to the Live Table. Or, create the new records in the Live Table with a flag to differentiate Temp records from Live records (Unposted vs Posted records), then when the user chooses to accept the changes, set the flag to Posted.

Personally, I'm a fan of the second option, a flag to Post records. The database has less of a chance of growing unnecessarily as temp records aren't created (as much). But it will add a little bit more complexity to your Forms, Queries and Report as you will have to take this flag into account. On the flip side, sometimes you just wanna keep it simple and using Temp Tables does this.

Once you decide which way you want to go, the code will sorta write itself. If using Temp Tables, you'll need a SQL INSERT into your Live Tables from the Temp Tables of all your recently added records, then a SQL DELETE to clean up the Temp Tables (which can be put into the Form Open). If you go with Posting, then you'll need a SQL UPDATE query to post the Temp Rows.