Edit table entry in form view

[colvind]

Hi there.
I had a question that may seem simple but I'm having some problems.

I have an Access DB.
One table: Contacts
Two forms: Short Contact Form and Long Contact Form
(only difference between the forms is there are some fields on
the long that don't appear on the short)
Both forms enter data via onto the Contacts table. (If using the
short form, the fields that don't appear on the short form are simply
blank on the table)

Two reports: Short Form report and Long Form Report
There's a button on each form that saves then dumps the form info
onto the appropriate form that looks good for printing.

I have a Query-by-Form style form/query setup to find previous records
and display them with in Datasheet view or the appropriate report.

However, I am at a loss to be able to reopen a record in the
appropriate form to edit that record. I can edit the table or in
datasheet view. But my users will not want to use either method
they'll simply want to be able to bring up the previous record in a
form for editting.

Is this possible? To have a record populate a form and be editted and
save the editted into back to the table?

 

[Klatuu]

This is normall done with an unbound Combo box. It allows the user to type
in a name and if found, make that nam'es record the current record in the
form. If the record doesn't exist, it allows the user to to either add a
new record, or go back and try again.

If you don't have a primary key field for your table, add one. I prefer
(although this is a subject for argument) an Autonumber field. Then you
create a combo with two columns. Once with the Autonumber primary key
field, and one to display the names. You make the first column of the combo
hidden by using the column widths property of the combo. Set this
properties for your combo:

Column Count = 2
Column Widths = 0";2.5" (0 makes the first column hidden, the 2.5 c an be
whatever you need to display the name
Bound Column = 1
Limit To List = Yes
Row Source Type = Table/Query

Now, your row source will look something like this, but use your own table
and field names:

SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",", " &
[FirstName]) AS FullName FROM tblClient;

You use the After Update event of the combo to make the selected record the
current record. Here is how this looks:

Private Sub cboClientSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboClientSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

If the value the user types in is not found, the Not In List event will fire
and allow you to either try again or it will add the name you typed in to
the table and position you on the new record:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub

And, of course, change the names to use your names.

 

[colvind]

This is normall done with an unbound Combo box.  It allows the user to type
in a name and if found, make that nam'es record the current record in the
form.  If  the record doesn't exist, it allows the user to to either add a
new record, or go back and try again.

If you don't have a primary key field for your table, add one.  I prefer
(although this is a subject for argument) an Autonumber field.  Then you
create a combo with two columns.  Once with the Autonumber primary key
field, and one to display the names.  You make the first column of the combo
hidden by using the column widths property of the combo.  Set this
properties for your combo:

Column Count = 2
Column Widths = 0";2.5"  (0 makes the first column hidden, the 2.5 c an be
whatever you need to display the name
Bound Column = 1
Limit To List = Yes
Row Source Type = Table/Query

Now, your row source will look something like this, but use your own table
and field names:

SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",", " &
[FirstName]) AS FullName FROM tblClient;

You use the After Update event of the combo to make the selected record the
current record.  Here is how this looks:

Private Sub cboClientSearch_AfterUpdate()
    With Me.RecordsetClone
        .FindFirst "[ClientID] = " & Me.cboClientSearch
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
End Sub

If the value the user types in is not found, the Not In List event will fire
and allow you to either try again or it will add the name you typed in to
the table and position you on the new record:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

    If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
            vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

        Me.cboClientSearch.Undo
        strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
        Me.Requery
        With Me.RecordsetClone
            .FindFirst "[MainName] = """ & NewData & """"
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
    Else
        Me.cboClientSearch.Undo
        Response = acDataErrContinue
    End If
End Sub

And, of course, change the names to use your names.

Hi there.
I had a question that may seem simple but I'm having some problems.

I have an Access DB.
One table: Contacts
Two forms: Short Contact Form and Long Contact Form
      (only difference between the forms is there are some fieldson
the long that don't appear on the short)
Both forms enter data via onto the Contacts table.  (If using the
short form, the fields that don't appear on the short form are simply
blank on the table)

Two reports:  Short Form report and Long Form Report
There's a button on each form that saves then dumps the form info
onto the appropriate form that looks good for printing.

I have a Query-by-Form style form/query setup to find previous records
and display them with in Datasheet view or the appropriate report.

However, I am at a loss to be able to reopen a record in the
appropriate form to edit that record.  I can edit the table or in
datasheet view.  But my users will not want to use either method
they'll simply want to be able to bring up the previous record in a
form for editting.

Is this possible?  To have a record populate a form and be editted and
save the editted into back to the table?- Hide quoted text -

- Show quoted text -

Klatuu (Niktow, Gort!)
I think I need some clarification.
Would I need to create a new form or would I use the existing forms to
make this work?
I do use a primary key, an autonumber.

I was able to create the combo box to your specifications.
Now the row source SELECT statement, I assume your designations are:
SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",",
" & [FirstName]) AS FullName FROM tblClient;

tblClient = name of my table, Contacts
ClientID = the autonumber field, I just call it ID in the Contacts
table
MainName, FirstName, FullName = ? I'm not sure these represent?
Fields I want to match?

In the After Update code, what does Me.Bookmark designate?

Sorry, I'm learning as I go.
I appreciate the help.

 

[colvind]

This is normall done with an unbound Combo box.  It allows the user to type
in a name and if found, make that nam'es record the current record in the
form.  If  the record doesn't exist, it allows the user to to either add a
new record, or go back and try again.

If you don't have a primary key field for your table, add one.  I prefer
(although this is a subject for argument) an Autonumber field.  Then you
create a combo with two columns.  Once with the Autonumber primary key
field, and one to display the names.  You make the first column of the combo
hidden by using the column widths property of the combo.  Set this
properties for your combo:

Column Count = 2
Column Widths = 0";2.5"  (0 makes the first column hidden, the 2.5 c an be
whatever you need to display the name
Bound Column = 1
Limit To List = Yes
Row Source Type = Table/Query

Now, your row source will look something like this, but use your own table
and field names:

SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",", " &
[FirstName]) AS FullName FROM tblClient;

You use the After Update event of the combo to make the selected record the
current record.  Here is how this looks:

Private Sub cboClientSearch_AfterUpdate()
    With Me.RecordsetClone
        .FindFirst "[ClientID] = " & Me.cboClientSearch
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
End Sub

If the value the user types in is not found, the Not In List event will fire
and allow you to either try again or it will add the name you typed in to
the table and position you on the new record:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

    If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
            vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

        Me.cboClientSearch.Undo
        strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
        CurrentDb.Execute strSQL, dbFailOnError
        Response = acDataErrAdded
        Me.Requery
        With Me.RecordsetClone
            .FindFirst "[MainName] = """ & NewData & """"
            If Not .NoMatch Then
                Me.Bookmark = .Bookmark
            End If
        End With
    Else
        Me.cboClientSearch.Undo
        Response = acDataErrContinue
    End If
End Sub

And, of course, change the names to use your names.

Hi there.
I had a question that may seem simple but I'm having some problems.

I have an Access DB.
One table: Contacts
Two forms: Short Contact Form and Long Contact Form
      (only difference between the forms is there are some fieldson
the long that don't appear on the short)
Both forms enter data via onto the Contacts table.  (If using the
short form, the fields that don't appear on the short form are simply
blank on the table)

Two reports:  Short Form report and Long Form Report
There's a button on each form that saves then dumps the form info
onto the appropriate form that looks good for printing.

I have a Query-by-Form style form/query setup to find previous records
and display them with in Datasheet view or the appropriate report.

However, I am at a loss to be able to reopen a record in the
appropriate form to edit that record.  I can edit the table or in
datasheet view.  But my users will not want to use either method
they'll simply want to be able to bring up the previous record in a
form for editting.

Is this possible?  To have a record populate a form and be editted and
save the editted into back to the table?- Hide quoted text -

- Show quoted text -

Klatuu (Niktow, Gort!)
I think I need some clarification.
Would I need to create a new form or would I use the existing forms to
make this work?
I do use a primary key, an autonumber.

I was able to create the combo box to your specifications.
Now the row source SELECT statement, I assume your designations are:
SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",",
" & [FirstName]) AS FullName FROM tblClient;

tblClient = name of my table, Contacts
ClientID = the autonumber field, I just call it ID in the Contacts
table
MainName, FirstName, FullName = ? I'm not sure these represent?
Fields I want to match?

In the After Update code, what does Me.Bookmark designate?

Sorry, I'm learning as I go.
I appreciate the help.

 

[colvind]

Klatuu (Niktow, Gort!)
I think I need some clarification.
Would I need to create a new form or would I use the existing forms to
make this work?
I do use a primary key, an autonumber.

I was able to create the combo box to your specifications.
Now the row source SELECT statement, I assume your designations are:
SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",",
" & [FirstName]) AS FullName FROM tblClient;

tblClient = name of my table, Contacts
ClientID = the autonumber field, I just call it ID in the Contacts
table
MainName, FirstName, FullName = ? I'm not sure these represent?
Fields I want to match?

In the After Update code, what does Me.Bookmark designate?

Sorry, I'm learning as I go.
I appreciate the help.

 

[Klatuu]

See comments in line below

This is normall done with an unbound Combo box. It allows the user to type
in a name and if found, make that nam'es record the current record in the
form. If the record doesn't exist, it allows the user to to either add a
new record, or go back and try again.

If you don't have a primary key field for your table, add one. I prefer
(although this is a subject for argument) an Autonumber field. Then you
create a combo with two columns. Once with the Autonumber primary key
field, and one to display the names. You make the first column of the
combo
hidden by using the column widths property of the combo. Set this
properties for your combo:

Column Count = 2
Column Widths = 0";2.5" (0 makes the first column hidden, the 2.5 c an be
whatever you need to display the name
Bound Column = 1
Limit To List = Yes
Row Source Type = Table/Query

Now, your row source will look something like this, but use your own table
and field names:

SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",", " &
[FirstName]) AS FullName FROM tblClient;

You use the After Update event of the combo to make the selected record
the
current record. Here is how this looks:

Private Sub cboClientSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboClientSearch
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

If the value the user types in is not found, the Not In List event will
fire
and allow you to either try again or it will add the name you typed in to
the table and position you on the new record:

Private Sub cboClientSearch_NotInList(NewData As String, Response As
Integer)
Dim strSQL As String

If MsgBox(NewData & " Is not in the list - Add " & NewData, vbQuestion +
_
vbYesNo + vbDefaultButton2, "Not Found") = vbYes Then

Me.cboClientSearch.Undo
strSQL = "INSERT INTO tblClient ( MainName )SELECT """ & NewData &
""" AS Dummy;"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[MainName] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Me.cboClientSearch.Undo
Response = acDataErrContinue
End If
End Sub

And, of course, change the names to use your names.

Klatuu (Niktow, Gort!)
I think I need some clarification.
Would I need to create a new form or would I use the existing forms to
make this work?

Use your current form.

I do use a primary key, an autonumber.

Good

I was able to create the combo box to your specifications.
Now the row source SELECT statement, I assume your designations are:
SELECT tblClient.ClientID, [MainName] & IIf([FirstName] Is Null,"",",
" & [FirstName]) AS FullName FROM tblClient;

tblClient = name of my table, Contacts

Yes

ClientID = the autonumber field, I just call it ID in the Contacts
table

Yes

MainName, FirstName, FullName = ? I'm not sure these represent?
Fields I want to match?

In this table The MainName can be either a person's last name or a company
name. FirstName is used only for persons. FullName is the Alias I used
when combining the fields in the query. Here you woul use whatever field
contains descriptive texts about the contact. ContactName,
ContactDescription, or whatever you use.

In the After Update code, what does Me.Bookmark designate?

Me referes to the current form
Bookmark is a property that uniquely identify a record. It is a bit
different in forms and recordset. Note the recordsetclone is a recordset,
so the Bookmark for it identifies the record. The form's bookmark is the
current record, so when you set the form's bookmark to its recordset's
bookmark, it becomes the current record.


Sorry, I'm learning as I go.
I appreciate the help.