Altering Form to data entry based on combo box

[Guest]

Dear all,

I have a form I want a user to be able to use to add new records or edit
existing ones. The user will enter an ID which he will not know if it is an
existing record or not and if it is existing then I'd like the user to see
the previous values but if it is new then I'd like them to see blank fields
to fill. I've set up a Combo box and linked it to the existing IDs using the
standard code:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I want to have a method which sets:

Me.Form.DataEntry = True

If the ID is not in the list. Any advice would be appreciated. As a smaller
question, I'd like the user to see blank values to begin with when the form
loads while just now they see the values of the initial record.

Cheers,

Alasdair

 

[Guest]

Here is a little trick that will, I think, do what you want. For your
search, use a combo box in the header section of the form, set the auto
expand property to yes and the limit to list property to no. Set the visible
property of the detail section of your form to No. This way, when the form
opens. the only thing visible will be the search combo.

Then in the Not In List event of the combo box:

If Msgbox("ID Not Found" & vbNewLIne & "Do You want to add this ID",
_vbQuestion + vbYesNo) = vbYes Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Me.Detail.Visible = True
End If

And modify you after update event to include:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Detail.Visible = true
End Sub

 

[Marshall Barton]

I have a form I want a user to be able to use to add new records or edit
existing ones. The user will enter an ID which he will not know if it is an
existing record or not and if it is existing then I'd like the user to see
the previous values but if it is new then I'd like them to see blank fields
to fill. I've set up a Combo box and linked it to the existing IDs using the
standard code:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I want to have a method which sets:

Me.Form.DataEntry = True

If the ID is not in the list. Any advice would be appreciated. As a smaller
question, I'd like the user to see blank values to begin with when the form
loads while just now they see the values of the initial record.

Instead of
Me.Form.DataEntry = True
you should try using
Me.AllowEdits = False
but, why not forget this idea and just move to a new record?

Your line
Set rs = Me.Recordset.Clone
should be
Set rs = Me.RecordsetClone


Make sure the combo box is unbound. If you're using DAO, I
think you can do it this way

Private Sub ComboBox_AfterUpdate()
If Me.ComboBox.ListIndex = -1 Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Else
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End If
End Sub

If you're using ADO, then you have to use the .Find method
instead of .FindFirst (and probably some other differences).

 

[Marshall Barton]

Klatuu wrote:
[snip]

Then in the Not In List event of the combo box:

If Msgbox("ID Not Found" & vbNewLIne & "Do You want to add this ID",
_vbQuestion + vbYesNo) = vbYes Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Me.Detail.Visible = True
End If

I think the NotInList event must complete before navigation
can occur. An alternative is to use the AfterUpdate event
and check if .ListIndex = -1

 

[Guest]

I have a form I want a user to be able to use to add new records or edit
existing ones. The user will enter an ID which he will not know if it is an
existing record or not and if it is existing then I'd like the user to see
the previous values but if it is new then I'd like them to see blank fields
to fill. I've set up a Combo box and linked it to the existing IDs using the
standard code:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I want to have a method which sets:

Me.Form.DataEntry = True

If the ID is not in the list. Any advice would be appreciated. As a smaller
question, I'd like the user to see blank values to begin with when the form
loads while just now they see the values of the initial record.

Instead of
Me.Form.DataEntry = True
you should try using
Me.AllowEdits = False
but, why not forget this idea and just move to a new record?

Your line
Set rs = Me.Recordset.Clone
should be
Set rs = Me.RecordsetClone


Make sure the combo box is unbound. If you're using DAO, I
think you can do it this way

Private Sub ComboBox_AfterUpdate()
If Me.ComboBox.ListIndex = -1 Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Else
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End If
End Sub

If you're using ADO, then you have to use the .Find method
instead of .FindFirst (and probably some other differences).

Marsh,

Thanks for the advice, you're right that I should be simply adding a record
instead of playing about with the Form's AllowEdits or DataEntry properties.
I'll give your code a try but first I'll have to work out how to make my
Combobox unbound.

Cheers,

Alasdair

 

[Guest]

Klatuu,

Thanks for the advice, your trick sounds simple but effective and I should
have been trying to add a record instead of anything else.

Cheers,

Alasdair

Here is a little trick that will, I think, do what you want. For your
search, use a combo box in the header section of the form, set the auto
expand property to yes and the limit to list property to no. Set the visible
property of the detail section of your form to No. This way, when the form
opens. the only thing visible will be the search combo.

Then in the Not In List event of the combo box:

If Msgbox("ID Not Found" & vbNewLIne & "Do You want to add this ID",
_vbQuestion + vbYesNo) = vbYes Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Me.Detail.Visible = True
End If

And modify you after update event to include:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Detail.Visible = true
End Sub

Dear all,

I have a form I want a user to be able to use to add new records or edit
existing ones. The user will enter an ID which he will not know if it is an
existing record or not and if it is existing then I'd like the user to see
the previous values but if it is new then I'd like them to see blank fields
to fill. I've set up a Combo box and linked it to the existing IDs using the
standard code:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I want to have a method which sets:

Me.Form.DataEntry = True

If the ID is not in the list. Any advice would be appreciated. As a smaller
question, I'd like the user to see blank values to begin with when the form
loads while just now they see the values of the initial record.

Cheers,

Alasdair

 

[Marshall Barton]

I have a form I want a user to be able to use to add new records or edit
existing ones. The user will enter an ID which he will not know if it is an
existing record or not and if it is existing then I'd like the user to see
the previous values but if it is new then I'd like them to see blank fields
to fill. I've set up a Combo box and linked it to the existing IDs using the
standard code:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I want to have a method which sets:

Me.Form.DataEntry = True

If the ID is not in the list. Any advice would be appreciated. As a smaller
question, I'd like the user to see blank values to begin with when the form
loads while just now they see the values of the initial record.

Instead of
Me.Form.DataEntry = True
you should try using
Me.AllowEdits = False
but, why not forget this idea and just move to a new record?

Your line
Set rs = Me.Recordset.Clone
should be
Set rs = Me.RecordsetClone


Make sure the combo box is unbound. If you're using DAO, I
think you can do it this way

Private Sub ComboBox_AfterUpdate()
If Me.ComboBox.ListIndex = -1 Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Else
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End If
End Sub

If you're using ADO, then you have to use the .Find method
instead of .FindFirst (and probably some other differences).

Thanks for the advice, you're right that I should be simply adding a record
instead of playing about with the Form's AllowEdits or DataEntry properties.
I'll give your code a try but first I'll have to work out how to make my
Combobox unbound.

Any control with a blank ControlSource property is unbound.
If the ControlSource property has the name of a field, then
it is bound to the field and anything you enter into the
control will be saved to the form's RecordSource table.

The reason I brought this issue up is that I was worried
that you may be trying to use a single combo box for two
different purposes, to enter data and to search for an
existing record. A typical arrangement for this kind of
thing is to have two combo boxes, the unbound search combo
box in the form's header section and the data entry combo
box in the detail section. I also like to set search combo
box's back color to something different so it's obvious that
it's no used for data entry.

 

[Guest]

Alasdair wrote:
I have a form I want a user to be able to use to add new records or edit
existing ones. The user will enter an ID which he will not know if it is an
existing record or not and if it is existing then I'd like the user to see
the previous values but if it is new then I'd like them to see blank fields
to fill. I've set up a Combo box and linked it to the existing IDs using the
standard code:

Private Sub ComboBox_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I want to have a method which sets:

Me.Form.DataEntry = True

If the ID is not in the list. Any advice would be appreciated. As a smaller
question, I'd like the user to see blank values to begin with when the form
loads while just now they see the values of the initial record.


Instead of
Me.Form.DataEntry = True
you should try using
Me.AllowEdits = False
but, why not forget this idea and just move to a new record?

Your line
Set rs = Me.Recordset.Clone
should be
Set rs = Me.RecordsetClone


Make sure the combo box is unbound. If you're using DAO, I
think you can do it this way

Private Sub ComboBox_AfterUpdate()
If Me.ComboBox.ListIndex = -1 Then
DoCmd.GoToRecord acDataForm, "MyForm", acNewRec
Else
' Find the record that matches the control.
With Me.RecordsetClone
.FindFirst "[Value] = '" & Me![ComboBox] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End If
End Sub

If you're using ADO, then you have to use the .Find method
instead of .FindFirst (and probably some other differences).

Thanks for the advice, you're right that I should be simply adding a record
instead of playing about with the Form's AllowEdits or DataEntry properties.
I'll give your code a try but first I'll have to work out how to make my
Combobox unbound.

Any control with a blank ControlSource property is unbound.
If the ControlSource property has the name of a field, then
it is bound to the field and anything you enter into the
control will be saved to the form's RecordSource table.

The reason I brought this issue up is that I was worried
that you may be trying to use a single combo box for two
different purposes, to enter data and to search for an
existing record. A typical arrangement for this kind of
thing is to have two combo boxes, the unbound search combo
box in the form's header section and the data entry combo
box in the detail section. I also like to set search combo
box's back color to something different so it's obvious that
it's no used for data entry.

Marsh,

I hope you had a good Xmas. I think you are right that I was trying to use
one combo box for two different purposes. I'd used unbound controls before in
setting up a search facility but here I wanted to also let the user see the
list of existing records so started working with a bound combo box. I'm going
to rethink this approach and your suggestion of two combo boxes seems like a
good idea.

Cheers,

Alasdair