Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding multiple records all at once (2000)

    Hi,
    Bit of a long one this....................
    Attached is a database that I'm working on.
    The user will be presented with FrmDefendant when the database is opened.
    They will fill in details in FrmDefendant, then fill in details in FrmWitnessSub. So each entry in FrmDefendant can have multiple entries in FrmWitness.
    This is as far as I've got. I've also created forms for the other end of the process, FrmInitialReceipt and FrmMediaMovement. The process with these two forms is that they are linked to TblMedia, which in turn is linked to TblWitness>TblDefendant. The user will record receipt of media in FrmInitialReceipt, and then record each individual movement of the media when it is sent to other departments/agencies. Basically keeping a record of when each copy of the media is moved and where to, when its returned, and where to.
    Q1: In FrmWitnessSub there is a command button that opens FrmAddMedia. In FrmMedia the user selects the media type(DVD,CD etc) and selects the amount of copies.The bit that I'm stuck on is how to add those copies to TblMedia (which will eventually be a form in FrmWitnessSub). The process I need to record is:
    User enter Defendant details followed by witness details. They click on CmdAddMedia and select 6 copies of a DVD. 6 copies of the DVD are added toTblMedia,which will be reflected in FrmWitnessSub, in a new form, FrmMedia. Copies 2 and 6 and sent to another department, so in FrmMedia, the user selects copy 2 and records the movement in FrmMediaMovement, solely filling in the fields DetailsOut and DateOut. The process is repeated for copy 6.
    Q2: How can I have a field in FrmMedia which displays whether the entry for each copy is in or out. namely, if DateOut isn't null and DateIn is null in FrmMediaMovement?

    FrmInitialReceipt is solely to record the initial receipt, the default details will fill as "In Office"(LocationInitial) and date of the record being registered(DateInitial), with the user being able to alter these if need be.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    Q1: here is the code:

    Private Sub CmdAddMedia_Click()
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim i As Integer

    On Error GoTo Err_CmdAddMedia_Click

    If IsNull(Me.Combo2) Then
    MsgBox "Please select a media type", vbExclamation
    Me.Combo2.SetFocus
    Exit Sub
    End If

    If IsNull(Me.Combo4) Then
    MsgBox "Please select a number of copies", vbExclamation
    Me.Combo4.SetFocus
    Exit Sub
    End If

    Set cnn = CurrentProject.Connection
    rst.Open "tblMedia", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    For i = 1 To Me.Combo4
    rst.AddNew
    rst!WitnessID = Forms!frmDefendant!FrmWitnessSub.Form.WitnessID
    rst!MediaTypeID = Me.Combo2
    rst!MediaCopy = Me.Combo4
    rst.Update
    Next i

    Exit_CmdAddMedia_Click:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    Err_CmdAddMedia_Click:
    MsgBox Err.Description, vbExclamation
    Resume Exit_CmdAddMedia_Click
    End Sub

    Note: please give your controls more meaningful names than Combo2 and Combo4! <img src=/S/please.gif border=0 alt=please width=31 height=23>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    Q2: what exactly do you want to see in that field? A number, or Yes/No, or <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding multiple records all at once (2000)

    Thanks for the coding again. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>
    Q2: Oops, literally 'In' or 'Out'.
    Generally the form that will be based on TblMedia (FrmMedia), I'm not too sure about.......
    I know how I'd like it to behave but I'm not too sure how to go about it. It will be a subform within FrmWitness, as will FrmInitialReceipt (with subform, FrmMediaMovement).
    The idea is that these two subforms (FrmWitness and FrmMediaMovement) will interact with each other, so:
    The user selects the copy from FrmMedia, then records the details of the medias movement in FrmMediaMovement. Once this is updated and DateIn is Null then the related entry in FrmMedia will be display as Out. Once the entry in FrmMediaMovement has a date in DateIn, the media will be shown as In.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    I'm not sure your table structure is correct. Why do you have separate tables tblInitialReceipt and tblMovement? It may well be necessary, but I don't understand the logic behind it.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding multiple records all at once (2000)

    The reasoning behind it is that the user will record the details of initial receipt of the media, and the date it was received. And then they could record the movement of the media. The problem I had in deciding whether to approach it like this was the user only records Initial Receipt once, but movement multiple times.
    In thinking about it, I presume that I could have TblInitialReceipt linked to TblMedia (One-To-One) , and TblMovement linked to TblMedia (One to Many) directly, as opposed to how it currently is. This would do the same thing.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    It depends on whether you want to trace media movement back to the media only or also to the initial receipt.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding multiple records all at once (2000)

    I solely need to record the initial receipt of the media so that it is visible to the user when they enter the media record. But once only for each entry in TblMedia.
    Its solely the movement that I need to trace of the media.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    Do you mean that there will only be one record for each MediaID in tblInitialReceipt? If so, you have a one-to-one relationship between tblMedia and tblInitialReceipt, and if fact, you don't need tblInitialReceipt at all, you can store the information in tblMedia itself.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding multiple records all at once (2000)

    Doh, of course....................I was maybe trying to split things too much!
    So back to my previous question, can I set a field value in TblMedia set to 'In' or 'Out' dependant on the value of DateIn?
    I'm trying to keep FrmMedia as a continuous form, so at a glance the user can see what copies are in. Likewise with FrmMediaMovement.
    Is it possible to link two subforms that are on a main form to each other, as opposed to the sub forms to the main form, namely FrmWitness, there by allowing both FrmMedia and FrmMediaMovement to be continuous forms within FrmWitness..............hope that makes sense!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    1. You shouldn't store the In/Out value in tblMedia, since it is a derived value. You can calculate it in a query, and use that query as record source for the media subform. Does the In/Out value depend solely on the most recent record in the media movement table for the MediaID?

    2. Yes, you can link two subforms. Make sure that your media subform has a text box MediaID; it may be hidden if you don't want the user to see it. Put a text box txtMediaID on frmWitness with control source

    =[NameOfMediaSubform]![MediaID]

    Set the Link Child Fields property of the media movement subform to MediaID (you should now have MediaID instead of InitialID in the media movement table), and the Link Master Fields property to txtMediaID.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding multiple records all at once (2000)

    Hi,
    1) That makes sense!
    2) Thanks for that. I currently can

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    You haven't created the recordset object before you open it. Add the following lines at the beginning of cmdAddMedia_Click:

    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim i As Integer

    The keyword New creates the recordset.

    You have a field MovementID in tblMedia. This is incorrect; you must remove the relationship between tblMedia and tblMovement, and create a relationship on MediaID instead, then remove the MovementID field from tblMedia. Otherwise, you won't be able to add records to tblMedia.

    You must requery the FrmWitnessSub subform after adding media:

    Forms!FrmDefendant!FrmWitnessSub!FrmMediaSub.Reque ry

    Note: you should require variable declaration in Tools | Options... in the Visual Basic Editor. This will add a line Option Explicit near the top of each new module; you should add this line manually in existing modules. Requiring variable declaration will be annoying at first, but it will save you a lot of grief in the long term. See for example <post#=314748>post 314748</post#>.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding multiple records all at once (2000)

    Right, I'm determined to get this right.............still having problems.
    I placed the appropriate code on CmdAddMedia, I've also changed the relationships between TblMedia and TblMovement via MediaID.

    Where do I place the requery coding, I presume this will be in the AfterUpdate event of FrmMediaSub.

    Also, with linking child and master fields, as TxtMediaId on FrmWitnessSub isn't a field in TblWitness, do I literally overtype the value in the properties.The wizard doesn't display this as an option for obvious reasons.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding multiple records all at once (2000)

    The line

    Forms!FrmDefendant!FrmWitnessSub!FrmMediaSub.Reque ry

    should go into the CmdAddMedia_Click procedure in FrmaddMedia, between Next i and Exit_CmdAddMedia_Click:.

    And yes, you should enter TxtMediaID in the Link Master Fields property of FrmMediaMovementSub yourself; the wizard can't handle this situation.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •