Creating multiple records from one form

[ajetrumpet]

Chill out Bro, just giving you a little ****

 

[rainman89]

Chill out Bro, just giving you a little ****

rough day so far man. sorry!

trying to figure this out. im almost at the point where i say... F-it!!

 

[ajetrumpet]

trying to figure this out. im almost at the point where i say... F-it!!

Trying to figure what out? The issue of this thread?? How could it be that important? It's not like your users are going to say "Oh well, this is too complicated, I'll go find another job. They wouldn't, would they!? Gosh, I hope not!

Actually, this problem is quite interesting. Like I said, I was trying to figure out how to use two arrays for this stuff, but I didn't get to the end of it. I might try working on this a bit later. There shouldn't be a problem using arrays for this. I wonder if one of the other gurus hear has any advice about this?

 

[rainman89]

Trying to figure what out? The issue of this thread?? How could it be that important? It's not like your users are going to say "Oh well, this is too complicated, I'll go find another job. They wouldn't, would they!? Gosh, I hope not!

Actually, this problem is quite interesting. Like I said, I was trying to figure out how to use two arrays for this stuff, but I didn't get to the end of it. I might try working on this a bit later. There shouldn't be a problem using arrays for this. I wonder if one of the other gurus hear has any advice about this?

yea the issue. and its not THAT important. just that everything else they have tasked me to do with this db has been done. dont like to dissapoint.

now adding this feature was supposed to cut down on time (not mine of course) but other peoples time when doing their entries.

ive been reading on how to try to use arrays to accomplish this like you said, but ive never done them before (cept in java) and frankly i cant grasp it.

 

[ajetrumpet]

I think this is though because you have a group of five values that aren't really related in anyway! Like say, I wanted to throw a whole column of values into one, using integers. Then, just loop through records like...

For intcounter = 1 to .Recorcount

But see, since you've got values that are not in common with one another, you can't do something like that.

 

[rainman89]

what if i created them all the same at first and then updated them based on the values in the combo boxes...

but then again, how would i know what ones to update... hmm

 

[ajetrumpet]

what if i created them all the same at first and then updated them based on the values in the combo boxes...

Created what the same??

 

[rainman89]

the records

 

[ajetrumpet]

The point I was trying to get at here Ray, is the reduction of code.

 

[rainman89]

oh i understand, but at this point im not gonna be picky about the size of the code, just want to get somtehing working, ill worry about the code later!

 

[Rabbie]

It might be worth repeating one of Doc_man's remarks. "If you cant do it on paper you cant do it in Access". Sit down with a sheet of paper and work out exactly what you want to do and how you are going to do it. Then coding it will be much easier.

 

[ajetrumpet]

at this point im not gonna be picky about the size of the code, just want to get somtehing working

In that case, write this...

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 str Like "cbocase*" then
        
        with rs
          .addnew
            !casefield = c
            !expensetype = me.cboexpensetype
            !expensename = me.txtexpensename
            !paymentmethod = me.cbopaymentmethod
            !amount = me.txtamount
            !paidto = me.txtpaidto
            !additionalinfo = me.txtadditionalinfo
          .update
        end with

      end if
    end if

  Next

End Sub

 

[rainman89]

Oh man its close..... just some tweaks... thanks adam... ill update when i get it going

 

[ajetrumpet]

It worked then? You'd have to change the combo boxes to start with "cboCase..." for this to work, but other than that, what tweaks were there? Interested....

 

[rainman89]

ok ive fixed the mismatch error, but now im getting a duplicate of the first record that is saved
anyone see why?

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

 

[ajetrumpet]

I wonder if it's because of the comma...??

 

[rainman89]

I wonder if it's because of the comma...??

you were correct. dont know how that got there

 

[ajetrumpet]

ok ive fixed the mismatch error, but now im getting a duplicate of the first record that is saved
anyone see why?

At the beginning of the code, you have comments about saving. Are you saving the information in the record somewhere in there?

A "DoCmd.Save", or equivalent, will automatically create a new record in the underlying table with all information that has been entered in the form. With that fact present, I would hope it would offer you an explanation of the problem. Does this help?

 

[rainman89]

no i dont have a duplicate save, only the addnew and the update

 

[ajetrumpet]

Well, the stuff I provided wouldn't cause a duplicate record. What it sounds like is the information that is entered in the form, before executing the code, is getting entered in the table by a save command, or something similar. Have you already posted the entire code? There was a module with more lines that you posted like 10 posts ago, but is that the entire sub? If it is, what process does the user go through? Is this just executed with the click of a button? You know, when the loop is done running, you should really loop though again and set all of the relevant controls (all of them that contain data) to NULL (Me.control = NULL).

Another thing you should check; I (think) that the command...

DoCmd.GoToRecord, , , acNewRec

...constitutes an automatic save of any information present. This would explain your duplicate first entry. Don't you have that command at the bottom of your sub somewhere (I remember seeing it in your post, before it disappeared)? I thought there was another post of yours with all of this code in it, but it seems as though it has disappeared!! Anyway, check that command, that would explain the problem perfectly. It would create a duplicate because you did not set the controls to NULL after running the loop...