How do I code VB to go to a new record, for an event in a form?

[Stormin'Norm]

I have a db with a combo box for searching for a given invoice #. If not
found, I would like to go to a new record. How would I code this in VB?

 

[Allen Browne]

I have a db with a combo box for searching for a given invoice #. If not
found, I would like to go to a new record. How would I code this in VB?

Can we assume:
- The search box is unbound (blank Control Source property)
- The InvoiceID field is type Number.
- The form has focus.
- The form is not filtered, and is not in DataEntry mode (where an existing
invoice number might not be found.)

The code should explicitly save any edits in progress. It then looks in the
RecordsetClone of the form to see if there is a match. If so, moves to that
record (by matching the form's Bookmark to the clone set.) If not, it goes
to a new record.

This kind of thing:

Dim strWhere As String
If Not IsNull(Me.Combo1) Then
If Me.Dirty Then Me.Dirty = False
strWhere = "[InvoiceID] = " & Me.Combo1
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
RunCommand acCmdRecordsGotoNew
Else
Me.Bookmark = .Bookmark
End If
End With
End If

 

[Stormin''''Norm]

Allen, your assumptions are correct except for the fact that the InvoiceID is
alpha-numeric. Will this require a separate function?

I have a db with a combo box for searching for a given invoice #. If not
found, I would like to go to a new record. How would I code this in VB?

Can we assume:
- The search box is unbound (blank Control Source property)
- The InvoiceID field is type Number.
- The form has focus.
- The form is not filtered, and is not in DataEntry mode (where an existing
invoice number might not be found.)

The code should explicitly save any edits in progress. It then looks in the
RecordsetClone of the form to see if there is a match. If so, moves to that
record (by matching the form's Bookmark to the clone set.) If not, it goes
to a new record.

This kind of thing:

Dim strWhere As String
If Not IsNull(Me.Combo1) Then
If Me.Dirty Then Me.Dirty = False
strWhere = "[InvoiceID] = " & Me.Combo1
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
RunCommand acCmdRecordsGotoNew
Else
Me.Bookmark = .Bookmark
End If
End With
End If

 

[Allen Browne]

If it's a text field, it needs extra quotes:
strWhere = "[InvoiceID] = """ & Me.Combo1 & """"

For an explanation, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, your assumptions are correct except for the fact that the InvoiceID
is
alpha-numeric. Will this require a separate function?

I have a db with a combo box for searching for a given invoice #. If
not
found, I would like to go to a new record. How would I code this in
VB?

Can we assume:
- The search box is unbound (blank Control Source property)
- The InvoiceID field is type Number.
- The form has focus.
- The form is not filtered, and is not in DataEntry mode (where an
existing
invoice number might not be found.)

The code should explicitly save any edits in progress. It then looks in
the
RecordsetClone of the form to see if there is a match. If so, moves to
that
record (by matching the form's Bookmark to the clone set.) If not, it
goes
to a new record.

This kind of thing:

Dim strWhere As String
If Not IsNull(Me.Combo1) Then
If Me.Dirty Then Me.Dirty = False
strWhere = "[InvoiceID] = " & Me.Combo1
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
RunCommand acCmdRecordsGotoNew
Else
Me.Bookmark = .Bookmark
End If
End With
End If

 

[Stormin''''Norm]

Allen, thanks for the guidance. The database and results are working as
required.

If it's a text field, it needs extra quotes:
strWhere = "[InvoiceID] = """ & Me.Combo1 & """"

For an explanation, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen, your assumptions are correct except for the fact that the InvoiceID
is
alpha-numeric. Will this require a separate function?

I have a db with a combo box for searching for a given invoice #. If
not
found, I would like to go to a new record. How would I code this in
VB?

Can we assume:
- The search box is unbound (blank Control Source property)
- The InvoiceID field is type Number.
- The form has focus.
- The form is not filtered, and is not in DataEntry mode (where an
existing
invoice number might not be found.)

The code should explicitly save any edits in progress. It then looks in
the
RecordsetClone of the form to see if there is a match. If so, moves to
that
record (by matching the form's Bookmark to the clone set.) If not, it
goes
to a new record.

This kind of thing:

Dim strWhere As String
If Not IsNull(Me.Combo1) Then
If Me.Dirty Then Me.Dirty = False
strWhere = "[InvoiceID] = " & Me.Combo1
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
RunCommand acCmdRecordsGotoNew
Else
Me.Bookmark = .Bookmark
End If
End With
End If