Results 1 to 9 of 9
  1. #1
    pickslides is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Location
    Melbourne, Australia
    Posts
    9

    Create a new form for Data Entry

    Hi there,



    I want a new form that uses the existing tables as drop downs.

    Each time I pick a student and incident (drop down from the tables), these should be added to a new table with a timestamp of when the record was added.

    It seems like this should be easy but I'm having problems getting it to work.

    DB with drop tables attached.
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, ditch the spaces (and don't use special characters, save for underscore _ in object names).
    See if this gets you started - it's about all that I've got time for at the moment.

    Note: in reality, I'd base the form on a query, not a table. That way you can show names instead of ID's (autonumbers), assuming you understand db normalization and how to set that up. You'd use additional textboxes for textual values and hide the ones I've entered that only contain the ID fields from your tables. The meaningful text would come from the other table fields that are not ID fields.

    The code for saving a record should check that both student and incident values have been chosen from the combos - not included.
    HTH
    Detention Tracker Draft.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by pickslides View Post
    ..., these should be added to a new table with a timestamp of when the record was added.
    I'm confused here! Do you mean you want an additional table, where incidents for every student are registered, or you want a new table every time when any incident for any student (i.e. a lot of single-incident tables) is registered? The 1st is OK, the 2nd is a NO-NO!

    To do this properly, you need a table like tblStudentIncidents: StudentIncidentID, StudentID, IncidentID, IncidentComment, RegisteringTime
    And I advice to redesign existing tables like
    tblStudents: StudentID, Forename, Surname;
    tblIncidents: IncidentID, IncidentDescription.

    To register incidents, you need either:
    A single form based on tblStudents, and a continuous subform based on tblStudentIncidents. Subform is linked to parent form by StudentID, and has controls like txtStudentIncidentID (hidden text box), txtStudentID (hidden text box), cbbIncident (visible combo box based on tblIncidents linked to tblStudentIncidents.IncidentID and displaying IncidentDescription), and txtRegisteringTime (hidden or disabled text box which has current datetime as default value). A new incident for active student is registered, when a new record in subform is saved (E.g. you create a new record in subform, select the incident there and enter it's description, and then leave the subform, select another record there, or start a next new record in subform. And until the record is saved, you can always abort the incident registering pressing ESC!).
    or
    A single unbound form with combos to select StudentID (e.g. cbbUnboundStudentID) and IncidentID (e.g. cbbUnboundIncidentID), with a continuous subform based on tblStudentIncidents. Subform is linked to parent form by pairs of StudentID, cbbUnboundStudentID and IncidentID, cbbUnvoundIncidentID. Probably at best you use disabled/locked combos linked to tblStudentIncidents.StudentID and tblStudentIncidents.IncidentID in subform, an editable text box to enter IncidentDescription, and again a hidden or disabled tect box with current datetime as default value. The subform will display all incidents of same type for student (depending on selections in unbound form). The rest is same as with 1st design.

  4. #4
    pickslides is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Location
    Melbourne, Australia
    Posts
    9
    Quote Originally Posted by Micron View Post
    First, ditch the spaces (and don't use special characters, save for underscore _ in object names).
    See if this gets you started - it's about all that I've got time for at the moment.

    Note: in reality, I'd base the form on a query, not a table. That way you can show names instead of ID's (autonumbers), assuming you understand db normalization and how to set that up. You'd use additional textboxes for textual values and hide the ones I've entered that only contain the ID fields from your tables. The meaningful text would come from the other table fields that are not ID fields.

    The code for saving a record should check that both student and incident values have been chosen from the combos - not included.
    HTH
    Detention Tracker Draft.zip

    Thanks for the reply, I can see you have added a form to populate the new table.

    Two quick questions, when I try to add a new record with the button it does not add to the table, have I not enabled something?

    Also the Timestamp, is there a function that can populate this for when the button is pushed?

  5. #5
    pickslides is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Location
    Melbourne, Australia
    Posts
    9
    Quote Originally Posted by ArviLaanemets View Post
    I'm confused here! Do you mean you want an additional table, where incidents for every student are registered, or you want a new table every time when any incident for any student (i.e. a lot of single-incident tables) is registered? The 1st is OK, the 2nd is a NO-NO!

    To do this properly, you need a table like tblStudentIncidents: StudentIncidentID, StudentID, IncidentID, IncidentComment, RegisteringTime
    And I advice to redesign existing tables like
    tblStudents: StudentID, Forename, Surname;
    tblIncidents: IncidentID, IncidentDescription.

    To register incidents, you need either:
    A single form based on tblStudents, and a continuous subform based on tblStudentIncidents. Subform is linked to parent form by StudentID, and has controls like txtStudentIncidentID (hidden text box), txtStudentID (hidden text box), cbbIncident (visible combo box based on tblIncidents linked to tblStudentIncidents.IncidentID and displaying IncidentDescription), and txtRegisteringTime (hidden or disabled text box which has current datetime as default value). A new incident for active student is registered, when a new record in subform is saved (E.g. you create a new record in subform, select the incident there and enter it's description, and then leave the subform, select another record there, or start a next new record in subform. And until the record is saved, you can always abort the incident registering pressing ESC!).
    or
    A single unbound form with combos to select StudentID (e.g. cbbUnboundStudentID) and IncidentID (e.g. cbbUnboundIncidentID), with a continuous subform based on tblStudentIncidents. Subform is linked to parent form by pairs of StudentID, cbbUnboundStudentID and IncidentID, cbbUnvoundIncidentID. Probably at best you use disabled/locked combos linked to tblStudentIncidents.StudentID and tblStudentIncidents.IncidentID in subform, an editable text box to enter IncidentDescription, and again a hidden or disabled tect box with current datetime as default value. The subform will display all incidents of same type for student (depending on selections in unbound form). The rest is same as with 1st design.


    Thanks for the reply. Yes, all entries should go into the one table.


    Unfortunately I don't understand most of what you have written, this is my weakness with coding.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    A simple example!

    I had do drop default value for registering time, as this got confusing, and instead added a couple of events coded in VBA.
    Attached Files Attached Files

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by pickslides View Post
    Two quick questions, when I try to add a new record with the button it does not add to the table, have I not enabled something?
    Also the Timestamp, is there a function that can populate this for when the button is pushed?
    Button works for me: go to new record, pick the combo values and click to save. Adding time stamp would be as

    Me.txtTimeStamp = Now()
    If Me.Dirty Then Me.Dirty = False

    I was letting you take care of the details around validating combo choices were made. I left out the timestamp part because you wouldn't want to timestamp if a combo choice was missing when the button was pushed. No doubt you have a better example now than my rushed effort.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    pickslides is offline Novice
    Windows 10 Access 2013
    Join Date
    Dec 2015
    Location
    Melbourne, Australia
    Posts
    9
    Quote Originally Posted by ArviLaanemets View Post
    A simple example!

    I had do drop default value for registering time, as this got confusing, and instead added a couple of events coded in VBA.

    This looks good, wondered why when I go to the data collection table that the ID values 1,2,3 etc were chosen rather than the drop down box text i.e. uniform, hair.

    Is there a way of making both of your forms into one?

    Drop down for both Student and Incident ?

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by pickslides View Post
    This looks good, wondered why when I go to the data collection table that the ID values 1,2,3 etc were chosen rather than the drop down box text i.e. uniform, hair.
    It is preferable to have a registry for repeated values entered into table field, with numeric/autonumeric ID, and then have those ID's instead real values as foreign key values there.
    a) A numeric field takes less resources - for field itself, and for indexing it;
    b) In case you need to edit the real registry values in future, having it's index in tables ensures, that you need to edit the value in registry only. In case you have instead of index the real text in all tables, you have to edit all records in database, where this value was used.
    In any form where source table contains such foreign keys, you use a combo as control, with as least 2 fields in RecordSource query. You set the foreign key in form's source as ControlSource, set ID field position in rowsource as BoundColumn value, set ColumnCount property to number of returned columns in RecordSource (e.g. 2 in combo in my example), and set ColunWidths property so, that the column with text values is displayed instead if ID field ("0;2.5" i my example - which means that ID-field of RecordSource query will be displayed with width 0, i.e. will be hidden, and 2nd one fills the whole combo. In Form view, the combo displays the text value, and when you select a new value, you also select the text value, but really an ID value is connected to combo instead.

    Quote Originally Posted by pickslides View Post
    Is there a way of making both of your forms into one? Drop down for both Student and Incident ?
    Not with single form. There is a possibility I described before (Unbound form with 2 combos, and with subform), but this will be more cumbersome. And you need a form to register students anyway! Having a subform in it to register all incidents linked to this student doesn't affect registerin new students in any way and is only a bonus feature.
    And in case you have some additional info linked with student, you can have several such subforms linked with student registering form. Instead of simply adding a subform, you can at first add a tab control, and then add subbforms one (or several) to every tab control page.

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

Similar Threads

  1. Replies: 6
    Last Post: 10-22-2022, 08:27 AM
  2. Create form for new data entry
    By marcvanderpeet12 in forum Forms
    Replies: 1
    Last Post: 08-08-2013, 07:46 AM
  3. Create LINK/LOOK UP in data entry form
    By marcvanderpeet12 in forum Forms
    Replies: 1
    Last Post: 08-08-2013, 07:44 AM
  4. create a button to open a form for data entry
    By dave john in forum Programming
    Replies: 3
    Last Post: 08-21-2012, 08:41 AM
  5. how to create data entry form using query
    By dave john in forum Programming
    Replies: 1
    Last Post: 08-21-2012, 06:00 AM

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