Creating multiple records from one form

[rainman89]

here is the entire code

Private Sub saveRecord_Click()
On Error GoTo Err_saveRecord_Click

    If IsNull(Me.txtExpenseDate) Or IsNull(Me.cboCaseNumber) Or IsNull(Me.txtExpenseAmount) Then
        Beep
        MsgBox "All required fields must be completed before you can save a record.", vbCritical, "Invalid Save"
        Exit Sub
    End If

    Beep
    Select Case MsgBox("Do you want to save your changes to the current record?" & vbCrLf & vbLf & "  Yes:         Saves Changes" & vbCrLf & "  No:          Does NOT Save Changes" & vbCrLf & "  Cancel:    Reset (Undo) Changes" & vbCrLf, vbYesNoCancel + vbQuestion, "Save Current Record?")
        Case vbYes: 'Save the changes
            Me.CLICKSAVE.Value = "Yes"
                Dim rs As Recordset
                Dim c As Control
                Dim str As String
                Set rs = Me.RecordsetClone
            
                    For Each c In Me.Controls
  
                        str = c.Name
  
                    If TypeOf c Is ComboBox Then
                            If Not IsNull(c) Then
                            
                        If str Like "cbocase*" Then
                        
                            With rs
                                .AddNew
                                !CaseID = c
                                !ExpenseTypeID = Me.cboExpenseType
                                !ExpenseName = Me.txtExpenseName
                                !PaymentMethodID = Me.cboPaymentMethod
                                !ExpenseAmount = Me.txtExpenseAmount
                                !ExpensePaidTo = Me.txtExpensePaidTo
                                !ExpenseAdditionalInfo = Me.txtExpenseAdditionalInfo
                                !TaskID = Me.TaskID
                                !ExpenseAddedby = Me.txtEmpInitials
                                !DateExpenseAdded = Me.TODAYDATE
                                !ExpenseReimbursement = Me.chkAssigntoAgent
                                !EmployeeID = Me.EmployeeID
                                !ExpenseDate = Me.txtExpenseDate
                                .Update
                        End With
                        End If
                        
                        End If
                End If
            Next
           
        Case vbNo: 'Do not save or undo
            'Do nothing

        Case vbCancel: 'Undo the changes
            DoCmd.RunCommand acCmdUndo
            Me.CLICKSAVE.Value = "No"

        Case Else: 'Default case to trap any errors
            'Do nothing

    End Select

Exit_saveRecord_Click:
    Exit Sub

Err_saveRecord_Click:
    If Err = 2046 Then 'The command or action Undo is not available now
        Exit Sub
    Else
        MsgBox Err.Description
        Resume Exit_saveRecord_Click
    End If
    
End Sub

i had the newrec code and thought the same thing, so i removed it, and its still doing the same thing.

 

[ajetrumpet]

What is this...

Me.CLICKSAVE.Value = "Yes"

??? just a txtbox?

Other than that dude, I have no idea. Nothing looks bad, the code looks fine to me. Corruption maybe? Although I doubt it. I don't know that much about corruption and its causes to say much more than this. I wish I could say that it was corruption, but I don't know the whole process here, wish I did (because there are a few other reasons for something like this, but it's too time consuming to discuss over the computer)...

 

[rainman89]

What is this...

Me.CLICKSAVE.Value = "Yes"

??? just a txtbox?

Other than that dude, I have no idea. Nothing looks bad, the code looks fine to me. Corruption maybe? Although I doubt it. I don't know that much about corruption and its causes to say much more than this. I wish I could say that it was corruption, but I don't know the whole process here, wish I did (because there are a few other reasons for something like this, but it's too time consuming to discuss over the computer)...

yea its just a text box to ensure that the record was saved. ill mess with it more today and see if i can figure it out. thanks again Adam. Ill update when i get alittle more time

 

[ajetrumpet]

yea its just a text box to ensure that the record was saved.

ENSURE that the record was saved? That word implies that the record was saved before the loop was executed. Be careful of your wording here! Are you sure the record was not saved somehow before the loop began? After all, to "ensure" means to "double check"!!

**FACT**
If the texbox says "yes", and you wrote the line of code to "ensure" the save, you have just caused the duplication...

 

[rainman89]

ENSURE that the record was saved? That word implies that the record was saved before the loop was executed. Be careful of your wording here! Are you sure the record was not saved somehow before the loop began? After all, to "ensure" means to "double check"!!

**FACT**
If the texbox says "yes", and you wrote the line of code to "ensure" the save, you have just caused the duplication...

was out of the office all day yesterday. sorry for not replying.

when i meant ensures, it just means that before you close the form, it checks to make sure that is was saved by checking the value in the clicksave txtbox. just makes sure it says yes before it will close the form.

if i have time today ill do some more testing to see if it is being saved before the loop is ran.

 

[rainman89]

Ok so ive done some more testing today and thought that maybe it was my null check that was causing the duplicate, but its not,


also there is only one combo box that is tied to the caseID, which is the first combobox, i removed its dependecny on that and am still getting duplicates.

i have checked all my code, and there is no duplicate save going on.

I cant see where the loop that we have been working on could start over either... im back to being stumped. I havent worked on it very much today, hopefully i will get more time tomorrow.

Thanks for any input.

 

[ajetrumpet]

Ray,

I'm not really sure what to tell you about this. I could probably give you an answer if I saw the database myself, but other than that...(???)

Let me know what you find out tomorrow.

 

[ajetrumpet]

Ray, this just occurred to me, but I'm not sure it will help you...

Have you looked at my Multiple Record Entry FAQ database file??

I wonder if there is anything in the coding of it that you could use...???

 

[rainman89]

didnt know it existed. Thanks

from what i saw though you used a temp table and inserts and deletes.... IMHO its risky using delete queries.... from my experience anyways... ill see if i can clean this db up and get rid of most of the info so that i can post it up here.

 

[ajetrumpet]

from what i saw though you used a temp table and inserts and deletes.... IMHO its risky using delete queries.... from my experience anyways...

Yes, it probably is, but how times would the VB code delete records from a table other than the one listed in the command line? Hopefully, NEVER! I would be questioning Microsoft a lot more than I do now if that ever did happen!

 

[rainman89]

HEre is a sample with just the form i am talking about. just click log in when the main form comes up and click the enter an expense sheet. you should see what im talking about. make sure all fields are filled in on the expense form including the expense date that is located above the combo boxes.

any questions feel free to ask.

 

[ajetrumpet]

Rainman,

when I push the save button, I don't see the records being saved anywhere. Are you sure it's working?

The subform has a recordsource of a query...the results should save the corresponding values to that query. It doesn't. Am I doing this wrong? You said before that it worked...all except for the duplicate record it was creating!

 

[ajetrumpet]

Nevermind my last post Ray.

I have no idea what you're talking about when you say you are getting a duplicate record. I enter as many case numbers as I want, and they all appear in the underlying query, which would mean that they would appear in the relevant source tables as well. What exactly are you noticing? It looks like it works just fine!

 

[rainman89]

ok i have done some more testing and it seems that when i close the form, the record is being saved again, because the value of the clicksave box is set to yes.... so i need to find a way to clear the whole form after i click the save button... thought that would work by going to a new record, but it didnt seem to work that way. any suggestions?

also trying to set each value to null in the form does not work. i keep getting the *you tried to assign a null value to a non null variant type* error.

 

[ajetrumpet]

I still don't see it Rainman.

When I open it, then open the form with "test" in the name, and save 3 records with 3 combo boxes, they appear in the underlying query. If I close the form, they still appear in the query. And there is not an extra record either. What are you talking about!?

 

[rainman89]

Ok see the pictures of what im seeing... dont know how to tell you otherwise..

figure one is when i click save it saves appropriately.

figure 2 is when i close the form the record gets duplicated... hope this helps

 

[ajetrumpet]

Ray,

I've got it figured out, and you aren't going to believe the result.

One question, how long have you been doing this kind of work???????

 

[rainman89]

just graduated from college so not long. first full database...


whhhhyyyy....... something noobish?

 

[ajetrumpet]

Well,

I'm still IN college, so chill out!

and YES, something NEWBISH!!!!!!

<EDIT> - too late! </edit> Oops, did I just reveal one more thing about myself!? I guess my secrecy days are over...

Just don't tell anyone else that this thread exists, OK? (even though it does have like 800 views already, it's not like it's OBVIOUS!!)

 

[rainman89]

well you sir, have far more knowledge in this than i.....

and no i wont tell anyone. it'll be our secret