Unbound data entry for more than 1 table

[DWHTHS]

Hello, I am quite new to access, but seem to be getting there now. I am producing a database, and I want to make a data entry form that is not bound to a table, as I do not want half completed records to be saved to the database.

I have used a append query for where I have information going into one table, and this worked great.

What I am trying to do now though, is have a data entry form for a main table 1, as well as another table 2 that has a one-to-many relationship. The common field is called ‘SchemeID’ which is an ‘autonumber’ in table 1, and a ‘number’ in table 2 (to enable more than one record to be stored [in table 2] for each of the main records [in table 1]).

Therefore my overall goal is a form/forms that can enter a single record onto the table 1 without saving (until the db is told to), and also be able to add multiple records to the table 2 (again, not until the db is told to do so), but the records in table 2 need to have the same autonumber that is generated from table 1.

I hope this makes sense! Can anyone think of an easy way to achieve this? Many thanks

[missinglinq]

I think you really need to rethink your entire project! Using unbound forms for data entry negates one of Access' best features, the ability to automatically save records with almost no effort. Saving records thru unbound forms is certainly are not something to be attempted by an admitted Access newby!

I am producing a database, and I want to make a data entry form that is not bound to a table, as I do not want half completed records to be saved to the database.

You don't need an unbound form to prevent half completed records from being saved! You simply place validation code in the Form's BeforeUpdate event to check the required fields, and cancel the save if there are fields missing data. Here’s some typical validation code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If IsNull(Me.Field1) Then
    MsgBox "Field1 is a required field and cannot be empty!"
    Cancel = True
    Me.Field1.SetFocus
    Exit Sub
  End If
  If IsNull(Me.Field2) Then
    MsgBox "Field2 is a required field and cannot be empty!"
    Cancel = True
    Me.Field2.SetFocus
    Exit Sub
  End If
End Sub

What I am trying to do now though, is have a data entry form for a main table 1, as well as another table 2 that has a one-to-many relationship. The common field is called ‘SchemeID’ which is an ‘autonumber’ in table 1, and a ‘number’ in table 2 (to enable more than one record to be stored [in table 2] for each of the main records [in table 1])

Bound forms/subforms.are used for this exact purpose! The main form holds the 'one' side of the relationship and the subform holds the 'many' side.

and also be able to add multiple records to the table 2

Unbound forms only hold one record at a time. Actually they don’t hold “records” at all, they hold data that can then be committed as a record in a table. You have to enter data for one record at a time, then save it, before you can ebter data for another record. You cannot enter data multiple records, as you do in a bound form.

Can anyone think of an easy way to achieve this?

There is no easy way to achieve this using unbound forms! Re-evaluate your project, keeping these things in mind, and let us know when you need help.

Welcome to Bytes!

Linq ;0)>

[DWHTHS]

I think you really need to rethink your entire project! Using unbound forms for data entry negates one of Access' best features, the ability to automatically save records with almost no effort. Saving records thru unbound forms is certainly are not something to be attempted by an admitted Access newby!

I am producing a database, and I want to make a data entry form that is not bound to a table, as I do not want half completed records to be saved to the database.

You don't need an unbound form to prevent half completed records from being saved! You simply place validation code in the Form's BeforeUpdate event to check the required fields, and cancel the save if there are fields missing data. Here’s some typical validation code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  If IsNull(Me.Field1) Then
    MsgBox "Field1 is a required field and cannot be empty!"
    Cancel = True
    Me.Field1.SetFocus
    Exit Sub
  End If
  If IsNull(Me.Field2) Then
    MsgBox "Field2 is a required field and cannot be empty!"
    Cancel = True
    Me.Field2.SetFocus
    Exit Sub
  End If
End Sub

Bound forms/subforms.are used for this exact purpose! The main form holds the 'one' side of the relationship and the subform holds the 'many' side.



Unbound forms only hold one record at a time. Actually they don’t hold “records” at all, they hold data that can then be committed as a record in a table. You have to enter data for one record at a time, then save it, before you can ebter data for another record. You cannot enter data multiple records, as you do in a bound form.



There is no easy way to achieve this using unbound forms! Re-evaluate your project, keeping these things in mind, and let us know when you need help.

Welcome to Bytes!

Linq ;0)>

Hello, many thanks for the advice, your way loooks better/easier than what I was trying to do!

Dave