Results 1 to 13 of 13
  1. #1
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49

    Multiple entries in one form

    Hello everyone, I'm hoping (again) for some advice on this problem I have:

    The concept is to monitor dialysis patients' medication. (Nearly) all are on some essential categories of meds, even though the specific medicines may be different. And then there are various other meds that belong to various, not essential categories. Each month there is a review of their medication and changes are made. Also, changes/additions etc of a single medicine can be made at any time.
    For the shake of simplicity, I will not present the actual tables, fields e.t.c

    The tables I thought of using for saving that data is as follows:

    tbl_MedsChanges
    Entry_ID Patient_ID Med_ID Med_Dose Start_Date
    1 2 1 xxxxxx Date()
    2 23 2 yyyyy Date()
    tbl_Meds
    Med_ID Med_Category_ID Med_Name
    1 1 Med1
    2 2 Med2
    tbl_MedCategories
    Med_Category_ID Med_Category
    1 Cat1
    2 Cat2





    I think that the tables are self explanatory

    In my form I would like to have some essential, "fixed" categories of medication (let's say Cat1, Cat2, Cat3), where the user can select specific meds and dosage from cascading combo boxes. (The cascading thing is no problem, I can do it)
    I would also like to have cascading comboboxes for other meds, that fall into other carious vategories (Cat4, Cat5 etc). Every time a new med is entered, a new set of comboboxes appear to be able to enter the next one

    So the final form would be something like that:
    Click image for larger version. 

Name:	form.jpg 
Views:	57 
Size:	55.1 KB 
ID:	34346

    For the second part with the "non-fixed" medication, I guess I could use a subform in continuous mode with one set of comboboxes, though I would like to have the total of the medication submitted with one move, along with the "fixed" meds.
    But for the part with the fixed medication, I don't know how to do it. I cannot use a continuous subform, since each entry falls into a different category. Also, these meds don't apply to absolutely every patient, there may blanks among them.

    Any ideas to get me started? I'm not looking for a complete solution of course, but some pointers on how is the best way to set this up.

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Create a small local "template" table to hold the categories that go into the fixed portion then when you go to a new record for the patient run an append query (into tbl_MedsChanges) to add the "fixed"records. If you have different "types" of patient taking different "fixed" meds add a patient type to the local "template" table and use that in your append query to add the right med for the right patient type.

    Cheers,
    Vlad

  3. #3
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    Thanx for the reply.

    Could you give me an example, because I have a hard time figuring out how to enter the entries of the "fixed" meds into a temp table?

    I had also thought to use a temporary table to insert the entries of the 2nd set of meds. This way the user can correct them, delete them etc and only when everything is ok, he can submit (apend) to the main, tbl_MedsChanges. This I can do, given that those meds will be in a coninuous subform.
    The first part, I'm stuck.

    Manolis

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Manolis,

    The first one is also a subform in datasheet or continuous view, you simply restrict its source to only the intended categories (in the query that you use for its record source join the required tables and add to the criteria Category=1 Or Category=2 or Category=3).

    Cheers,
    Vlad

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I don't know why you would use a temp table, but then again, I don't know your requirements.

    As far as HOW to create the display, maybe this example will help you or give you some ideas.

    FYI, not all of the tables have data entered....



    Good luck with your project......
    Attached Files Attached Files

  6. #6
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    I'm sorry for being late to reply.
    I'll just try your suggestions and come back.
    Steve, thank you very much for taking the time to create such a complete example. The choice of Meds is somehow strange

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The choice of Meds is somehow strange
    LOL. Amazing what you can get on the NHS these days
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The choice of Meds is somehow strange
    I did a search for Category 1 Meds and guess what the first thing that popped up was??
    So I went with it and .......

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    My suggestion to use a local temp table was to allow for the "fixed" component to be predetermined by patient type, otherwise the design would be just like Steve's. The temp would be used to pre-populate (via an append query) the first subform with same three records (Cat1, Cat2, Cat3) or with three specific records based on patient type or any other criteria.

    Cheers,
    Vlad

  10. #10
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    I'm sorry I haven't reply to this for several days, but I had some other emergencies.
    Actually, what I was trying at my spare time, was what Vlad suggested in his last post. It seems to be working as I like to. I'll post any updates as soon as I can

    In the meanwhile could you help me with an update query I want to use for that?

    I want to update a record in my main table with data from my temporary record. The two tables have identical structure, and the records in question have the same PK
    The code I'm using is as follows:

    Code:
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM tbl_TEMP_Meds_Changes WHERE Med_StopDate IS NOT NULL AND ID IS NOT NULL AND New_Entry = TRUE;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                While (Not .EOF)
                    dbs.Execute "UPDATE tbl_Meds_Changes " & _
                    "SET tbl_Meds_Changes.Med_EntryDate = " & rs.Fields("Med_EntryDate") & "WHERE tbl_Meds_Changes.MedChange_ID = " & rs.Fields("ID") & ";"
                    .MoveNext
                Wend
            End If
            .Close
        End With
    The problem is that while my temporary table stores and displays Med_EntryDate correctly (it is a date in short format), when the actual update takes place, the main tables stores a "30/12/1899" value. I tried a
    Code:
    Debug.Print rs.Fields("Med_EntryDate")
    and it does display the date correctly.
    Both temp and main tables have the according field set to date, short.
    When I try to update other, non-date fields everything works fine. Any ideas?

  11. #11
    jabarlee is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    49
    ok, I was little to quick to ask for help, I managed to google it successfully :
    DAO and Jet have not the same date format, I had to change it during my UPDATE statement:
    Code:
    "SET tbl_Meds_Changes.Med_EntryDate = #" & Format(rs.Fields("Med_EntryDate"),"dd/mm/yyyy") & "# WHERE tbl_Meds_Changes.MedChange_ID = " & rs.Fields("ID") & ";"

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Whatever your regional settings, you need to format the dates as mm/dd/yyyy for use in your code or you will get errors on dates such as 03/11/2017. 3 Nov or 11 Mar?

    Your statement abut DAO and JET doesn't make sense.
    JET was the database engine before A2007 when it was replaced by ACE.
    the sentence is still meaningless if you wrote DAO and ACE ...

    Perhaps you are referring to the need for delimiters in VBA code?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see Colin pointed out the need for date delimiters and that the date needs to (must) be in American format.

    I modified your code a little:
    Code:
    Dim rs As DAO.Recordset
    Dim strSQL As String
    
    strSQL = "SELECT * FROM tbl_TEMP_Meds_Changes WHERE Med_StopDate IS NOT NULL AND ID IS NOT NULL AND New_Entry = TRUE;"
    Set rs = CurrentDb.OpenRecordset(strSQL)
        With rs
            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                While (Not .EOF)
                    strSQL = "UPDATE tbl_Meds_Changes" & _
                    strSQL = strSQL & " SET tbl_Meds_Changes.Med_EntryDate = #" & Format(rs.Fields("Med_EntryDate"),"mm/dd/yyyy") & "#" 
                    strSQL = strSQL & " WHERE tbl_Meds_Changes.MedChange_ID = " & rs.Fields("ID") & ";"
                    ' Debug.Print strSQL
                    dbs.Execute 
    
                    .MoveNext
                Wend
            End If
            .Close
        End With
    The Debug command allows you to see if the SQL string is configured correctly before it is executed.

    Also, I am curious about the "dbs" (in RED above). I do not see a declaration for the "dbs" nor where it is set to CurrentdB.
    Maybe you cut lot some/most of the code for the sub?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. A form that makes multiple entries
    By dimits in forum Forms
    Replies: 3
    Last Post: 06-09-2014, 12:53 PM
  2. Replies: 2
    Last Post: 03-05-2014, 12:16 PM
  3. Multiple data entries on a form
    By argsemapp in forum Forms
    Replies: 1
    Last Post: 12-03-2013, 03:04 PM
  4. Importing Multiple entries into a form
    By KayZee08 in forum Import/Export Data
    Replies: 3
    Last Post: 03-12-2013, 01:16 PM
  5. Multiple entries wanted in Form
    By taspangler in forum Access
    Replies: 3
    Last Post: 05-02-2011, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums