Form to Create New Records

[kwooten]

I have not used Access in a couple years and am now using Access 2016. Before I beat my brain dead I would like to know if my idea is even possible. Currently we have a process of granting new access to an application that requires editing 2 different files, one for the group the user needs to access and another that gives the level of access needed which can be more than one. I want to create a form that gives me the ability to make all the additions in one place that will create a new record as an append to the data I already have so that I can then use it to give access. Within our system we still have to upload 2 files but I would be fine making one table with all the information then I can use a query to carve out what I need and saving those 2 files; actually our IT team loads the files from a shared location.

From the examples I have seen that closely mirror what I want to do looks like I can do it without writing any code. The forms use embedded macros.

[Micron]

Your post is confusing.

requires editing 2 different files

Sounds like this has nothing to do with Access doing the editing, so I don't get the relevance and have no idea what the 2 files are.

use a query to carve out what I need and saving those 2 files

Queries don't save files - more confusion.
Let's try to cut to the chase. If you can create a query for permissions and levels that involves one or more tables, and you can edit data in the records that the query returns, then you can also append data based on that table and/or the joins between the tables that the query represents. In other words, if you cannot edit the records of that query, it cannot provide the basis for you to make appends, let alone edits. When you have an editable query, you can base a form on it. How you design and use that form depends on things like, do you want to open it for adding new records only, or show existing plus allow you to edit and add new?

You will probably need a form that contains all the controls in the detail section - unless there is a one (new user) to many (permissions) type of relationship.
If this doesn't help, you might have to provide a better explanation and avoid jargon and ambiguity. For example, when one mentions "access" it can be difficult to determine if this is Access or just access (permissions).

[kwooten]

Thanks, I will try to make this easier.. I want to edit a table by using a form to add new records. On this form I would like to have a section that adds to one table and a section to add to another, hence the 2 files because that is what our IT team needs. Currently we have to open and edit the 2 files manually so I am trying to consolidate the process.

[Micron]

I'm going to presume by "files" you mean Access database files that contain these tables (though you could be referring to csv files, text files, spreadsheets...).
If you link the required table (that is in each file) to the db that contains your forms and queries, I see two possible approaches.

1) you could design a query that would allow you to edit the data as I already mentioned. If you accomplish this, you'd build a form that is based on that query (a single form can only have one recordsource at a time) which would allow you to navigate through the records. When you navigate beyond the last record, you are automatically on a new record, which when saved, would update your tables. If you cannot edit the data in this query, this approach isn't valid as you won't be able to add records either - unless there is something wrong with the underlying design that you can fix. For example, some queries are not editable by design, such as Unions or those that use aggregate functions (or if I recall correctly, those that use multi-value fields).

2) A form that has 2 subform controls, each subform containing records from one of those tables. I cannot tell from the limited info if you need to base each subform on their respective tables or design a query for each. Either way, if you have created relationships between these tables it could be problematic in that you may have created a situation whereby you cannot add records in one subform because there's no related child record in the other subform. Your table situation suggests they are independent, so I don't think that's likely but felt it necessary to raise the potential. The main form which contains the 2 subs could provide the means to filter the records if need be, but if all you're doing is adding records, I don't see a need for that.

Speaking of which, as I type, I wonder if the two tables contain the same info. You haven't been very clear on what they are (or even if they're tables as I pointed out in my first paragraph here) or what their relationship is to anything, nor an idea of why they are in separate places. After all this, it could be as simple as copying the info from one place to another, though that is seldom a good idea if data integrity is of any importance.

[kwooten]

I like the form with 2 subform idea if possible. The tables are all stand alone.

First I have to have all the users so I would have a linked table that would keep the User table updated. I also have "lookup" tables; one for the group, one labeled as "PasswordAdapter" (if the user is an employee then he or she is assigned as PasswordAdapter, if the employee is a contractor then he or she is labeled ThirdPartyUser) and another as PurchasingUnit which is list of available units to assign the user to with the group needed.

The GroupUserMap would contain UserID from User table, PasswordAdapter that would be selected and the group to assign.
ResponsibleUser would contain UserID, PurchasingUnit (if more than one then the updated table would have multiple rows), PasswordAdapter and Group.

In the form I would like to select the UserID, Group, PasswordAdapter and would prefer to have check boxes for PurchasingUnit which would create the multiple records.

[kwooten]

See attached for the 2 files that am trying to get one form to update. The other file in the zip is the list of groups I want to be able to select from. I also have a stand alone table for the "Purchasing Units" I can select. Is there a way to select them individually and without having to go through the process by the number of units I need to add? If I have to create a record for each of the 3 units then I really would like to avoid assigning the user to the group 3 times.

I did create a form, based on GroupSharedUser and have the UserID as a text box then a combo for Adapter and Group; 2 subforms for GroupSharedUser and ResponsibleUser. I can put in the UserID, Adapter and Group and the subform will update the table to reflect the add in GroupSharedUser but the ResponsibleUser does not update. I know it is because the forms record source is GroupSharedUser but do not know how to get it to update the ResponsibleUser either.. Or if it is even possible because the ResponsibleUser will have data that wont be in GroupSharedUser, however everything in GroupSharedUser will be in ResponsibleUser..

[ssanfu]

Were you going to include your Access dB??? Or a copy of the dB with just a few records??

[kwooten]

Yep.. forgot to add that one.

Were you going to include your Access dB??? Or a copy of the dB with just a few records??

[ssanfu]

I am still very confused as to what you want or are trying to accomplish.

Leaving the issues of the table design, relationships and form design for later,
it seems to me that you are trying to:
1) enter users into the table "GroupSharedUser", then enter child records in the table "ResponsibleUser"
2) then you want to export the records into 2 CSV files ("GroupSharedUserMap_example" and "ResponsibleUser_Example")
3) then your IT team loads the CSV files (to another database?)

Am I close?

[kwooten]

I have never created a form so I was just playing around to see if I could get something to work.

I want to be able to enter a user number, select the adapter, group and assign it to a purchasing unit or multiple purchasing units.. Everything in ResponsibleUser will be in GroupShared so I would think ResponsibleUser would be the parent and GroupShared the child, right? You are correct on points 2 and 3.

[ssanfu]

I see a table named "UserList_tbl". Are you wanting to select the UserID from this table or do you want to type the UserID into a text box?

What about the linked table "UserList"?

[ssanfu]

Play with this dB.

Type the UserID into a text box.
If the UserID exists,
-the two subforms will display the data.
else
- select from the combo boxes in the two subforms.



This is just one way..... another is an all code method....

[kwooten]

Play with this dB.

Type the UserID into a text box.
If the UserID exists,
-the two subforms will display the data.
else
- select from the combo boxes in the two subforms.



This is just one way..... another is an all code method....

This is perfect.. I can play with this and figure out what I should have done.. Although I dont think I will ever figure out this event code on the Create CSV Files. I thought this would have been much easier than this.. maybe it was to an Access genius. If I could hire you to fix this other database my department has then I would.

I really appreciate your help.

[kwooten]

How can the code be updated to be an append to the full dataset? When IT loads these files it has to be the entire file not just the new records.

Play with this dB.

Type the UserID into a text box.
If the UserID exists,
-the two subforms will display the data.
else
- select from the combo boxes in the two subforms.



This is just one way..... another is an all code method....

[ssanfu]

How can the code be updated to be an append to the full dataset?

Not sure what you mean..

You want EVERY record in tables "GroupSharedUser" and "ResponsibleUser" in the CSV file?

I thought this would have been much easier than this..

Well, one way or another, you have to create and open the text file, write to it, then close it.
I wrote the code to create the CSV files "Old school" creating the queries in line.

Here is another version using saved queries (saves about 20 lines with the way I wrote the SQL):

Private Sub cmdCreateCSV_Click()  ' old school using saved queries
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim sSQL As String
    Dim TF As Integer
    Dim FileToWrite As String

    Set d = CurrentDb

    'GroupSharedUserMap
    Set r = d.OpenRecordset("qryGroupSharedUserToCSV")
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        'open CSV file for writing
        TF = FreeFile
        FileToWrite = CurrentProject.Path & "\" & "GroupSharedUserMap.CSV"
        Open FileToWrite For Output As #TF
        Print #TF, "UserID,Adapter,UserGroup"
        Do While Not r.EOF
            Print #TF, r!UserID & "," & r!Adapter & "," & r!AribaGroup
            r.MoveNext
        Loop
        Close #TF
        r.Close
    End If

    ' ResponsibleUser
    Set r = d.OpenRecordset("qryResponsibleUserToCSV")
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        'open CSV file for writing
        TF = FreeFile
        FileToWrite = CurrentProject.Path & "\" & "ResponsibleUser.CSV"
        Open FileToWrite For Output As #TF
        Print #TF, "Group,UniqueName,PurchasingUnit,PasswordAdapter"
        Do While Not r.EOF
            Print #TF, r!AribaGroup & "," & r!UserID & "," & r!PurchasingUnit & "," & r!Adapter
            r.MoveNext
        Loop
        Close #TF
        r.Close
    End If

   'clean up
    Set r = Nothing
    Set d = Nothing

    MsgBox "Done!" & vbNewLine & vbNewLine & "Saved in folder " & CurrentProject.Path
End Sub

And here is another version using the File System Object (approx the same number of lines as the version above):

Private Sub cmdCreateCSV_Click()   'using File Systen Object
    Dim d As DAO.Database
    Dim fs As Object
    Dim r As DAO.Recordset
    Dim a As Object

    Set d = CurrentDb
    Set fs = CreateObject("Scripting.FileSystemObject")

    'GroupSharedUserMap
    Set r = d.OpenRecordset("qryGroupSharedUserToCSV")
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        'open CSV file for writing
        Set a = fs.CreateTextFile(CurrentProject.Path & "\" & "GroupSharedUserMap.CSV", True)
        a.WriteLine ("UserID,Adapter,UserGroup")
        Do While Not r.EOF
            a.WriteLine r.Fields(0)
            r.MoveNext
        Loop
        a.Close
        r.Close
    End If

    ' ResponsibleUser
    Set r = d.OpenRecordset("qryResponsibleUserToCSV")
    If Not r.BOF And Not r.EOF Then
        r.MoveLast
        r.MoveFirst

        'open CSV file for writing
        Set a = fs.CreateTextFile(CurrentProject.Path & "\" & "ResponsibleUser.CSV", True)
        a.WriteLine ("Group,UniqueName,PurchasingUnit,PasswordAdapter")
        Do While Not r.EOF
            a.WriteLine r.Fields(0)
            r.MoveNext
        Loop
        a.Close
        r.Close
    End If

    'clean up
    On Error Resume Next
    Set a = Nothing
    Set r = Nothing
    Set fs = Nothing
    Set d = Nothing

    MsgBox "Done!" & vbNewLine & vbNewLine & "CSV files saved in folder " & CurrentProject.Path

End Sub

This version prints EVERY record to the CSV files..........