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

  • Thread starter Thread starter Stormin'Norm
  • Start date Start date
S

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?
 
Stormin'Norm said:
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?
Click to expand...

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, your assumptions are correct except for the fact that the InvoiceID is
alpha-numeric. Will this require a separate function?

Allen Browne said:
Stormin'Norm said:
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?
Click to expand...

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
Click to expand...
 
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.

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

Allen Browne said:
Stormin'Norm said:
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?
Click to expand...

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
Click to expand...
Click to expand...
 
Allen, thanks for the guidance. The database and results are working as
required.

Allen Browne said:
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.

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

Allen Browne said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top