Combo Box Integrity

[Guest]

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks

 

[Guest]

Sprinks,

As there is no option to tab On Current within properties of my combo box
should it be stored within Afer Update? Or should i be donig this within the
form properties?

My VBA currently reads as:
Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

What alterations do i need to make?

Many Thanks,
Matt

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

Matt,

On Current is a Form event that is triggered when you move to another
record. But you don't need to use it--simply modify your AfterUpdate event
procedure:

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

Set rs = Me.Recordset.Clone

' Use the current value of the combo box to find the 1st matching record
rs.FindFirst "[Request ID] = '" & Me![Combo24]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Reset the combo box to a blank string
Me![Combo24] = ""

End Sub

Also, if you're planning to get into Access VBA programming, the first smart
thing to do is to name your form controls meaningfully, preferably per
established naming conventions which can be found in any good Access
reference.

Bound controls are normally named by the field to which they are Bound
prefaced by a three-character prefix that identifies the control type, e.g.,

txtLName
cboCustomerID
chkActive

or, in your case, cboRequestIDLookup

Following these conventions make your programming life *much* easier.
Should you choose to rename this particular control, unfortunately, Access
does not automatically rename your event procedure. To rectify, create a new
AfterUpdate event procedure for the renamed control, cut and paste the body
of the old procedure into the new one, and delete the old.

Hope that helps.

Sprinks

Sprinks,

As there is no option to tab On Current within properties of my combo box
should it be stored within Afer Update? Or should i be donig this within the
form properties?

My VBA currently reads as:
Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

What alterations do i need to make?

Many Thanks,
Matt

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

Sprinks,

Right ok thankyou.
Maybe i wont go into the VBA route as i am puzzled about it all, was mainly
showing it to you as you might understand where I was going wrong

From your procedure i get an error message with run time error 3077 about a
syntax error within the following expression:

rs.FindFirst "[Request ID] = '" & Me![Combo24]

That seems to be the only problem I believe and i am just not going to
bother going into it anymore after this

Many Thanks
Matt

Matt,

On Current is a Form event that is triggered when you move to another
record. But you don't need to use it--simply modify your AfterUpdate event
procedure:

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

Set rs = Me.Recordset.Clone

' Use the current value of the combo box to find the 1st matching record
rs.FindFirst "[Request ID] = '" & Me![Combo24]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Reset the combo box to a blank string
Me![Combo24] = ""

End Sub

Also, if you're planning to get into Access VBA programming, the first smart
thing to do is to name your form controls meaningfully, preferably per
established naming conventions which can be found in any good Access
reference.

Bound controls are normally named by the field to which they are Bound
prefaced by a three-character prefix that identifies the control type, e.g.,

txtLName
cboCustomerID
chkActive

or, in your case, cboRequestIDLookup

Following these conventions make your programming life *much* easier.
Should you choose to rename this particular control, unfortunately, Access
does not automatically rename your event procedure. To rectify, create a new
AfterUpdate event procedure for the renamed control, cut and paste the body
of the old procedure into the new one, and delete the old.

Hope that helps.

Sprinks

Sprinks,

As there is no option to tab On Current within properties of my combo box
should it be stored within Afer Update? Or should i be donig this within the
form properties?

My VBA currently reads as:
Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

What alterations do i need to make?

Many Thanks,
Matt

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks


:

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

Matt,

My apologies. I'd thought you'd added code to the end of your Rs.Findfirst
line. Return it to what it was, adding my new line at the end:

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Combo24] = ""

End Sub

Hope that helps.

Sprinks

Sprinks,

Right ok thankyou.
Maybe i wont go into the VBA route as i am puzzled about it all, was mainly
showing it to you as you might understand where I was going wrong

From your procedure i get an error message with run time error 3077 about a
syntax error within the following expression:

rs.FindFirst "[Request ID] = '" & Me![Combo24]

That seems to be the only problem I believe and i am just not going to
bother going into it anymore after this

Many Thanks
Matt

Matt,

On Current is a Form event that is triggered when you move to another
record. But you don't need to use it--simply modify your AfterUpdate event
procedure:

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

Set rs = Me.Recordset.Clone

' Use the current value of the combo box to find the 1st matching record
rs.FindFirst "[Request ID] = '" & Me![Combo24]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Reset the combo box to a blank string
Me![Combo24] = ""

End Sub

Also, if you're planning to get into Access VBA programming, the first smart
thing to do is to name your form controls meaningfully, preferably per
established naming conventions which can be found in any good Access
reference.

Bound controls are normally named by the field to which they are Bound
prefaced by a three-character prefix that identifies the control type, e.g.,

txtLName
cboCustomerID
chkActive

or, in your case, cboRequestIDLookup

Following these conventions make your programming life *much* easier.
Should you choose to rename this particular control, unfortunately, Access
does not automatically rename your event procedure. To rectify, create a new
AfterUpdate event procedure for the renamed control, cut and paste the body
of the old procedure into the new one, and delete the old.

Hope that helps.

Sprinks

Sprinks,

As there is no option to tab On Current within properties of my combo box
should it be stored within Afer Update? Or should i be donig this within the
form properties?

My VBA currently reads as:
Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

What alterations do i need to make?

Many Thanks,
Matt




:

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks


:

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

Sprinks,

Ok still no luck, but am determined not to give it up as a bad job

The error message now is: Run time Error 3020, Update or cancelupdate
without addnew or edit. It has highlighted the bottom line i.e. just
Me![Combo24] = "" out of the whole thing as below

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Combo24] = ""

End Sub

Cheers,
Matt

Matt,

My apologies. I'd thought you'd added code to the end of your Rs.Findfirst
line. Return it to what it was, adding my new line at the end:

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Combo24] = ""

End Sub

Hope that helps.

Sprinks

Sprinks,

Right ok thankyou.
Maybe i wont go into the VBA route as i am puzzled about it all, was mainly
showing it to you as you might understand where I was going wrong

From your procedure i get an error message with run time error 3077 about a
syntax error within the following expression:

rs.FindFirst "[Request ID] = '" & Me![Combo24]

That seems to be the only problem I believe and i am just not going to
bother going into it anymore after this

Many Thanks
Matt

Matt,

On Current is a Form event that is triggered when you move to another
record. But you don't need to use it--simply modify your AfterUpdate event
procedure:

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

Set rs = Me.Recordset.Clone

' Use the current value of the combo box to find the 1st matching record
rs.FindFirst "[Request ID] = '" & Me![Combo24]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Reset the combo box to a blank string
Me![Combo24] = ""

End Sub

Also, if you're planning to get into Access VBA programming, the first smart
thing to do is to name your form controls meaningfully, preferably per
established naming conventions which can be found in any good Access
reference.

Bound controls are normally named by the field to which they are Bound
prefaced by a three-character prefix that identifies the control type, e.g.,

txtLName
cboCustomerID
chkActive

or, in your case, cboRequestIDLookup

Following these conventions make your programming life *much* easier.
Should you choose to rename this particular control, unfortunately, Access
does not automatically rename your event procedure. To rectify, create a new
AfterUpdate event procedure for the renamed control, cut and paste the body
of the old procedure into the new one, and delete the old.

Hope that helps.

Sprinks


:

Sprinks,

As there is no option to tab On Current within properties of my combo box
should it be stored within Afer Update? Or should i be donig this within the
form properties?

My VBA currently reads as:
Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

What alterations do i need to make?

Many Thanks,
Matt




:

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks


:

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt

 

[Guest]

Matt,

I still screwed you up; my apologies. First, be sure your lookup combo box
is Unbound, that is, its ControlSource property is blank.

The syntax for the rs.Findfirst line depends on whether the *value* of the
combo is text or numeric. The *value* of the row selected is the value in
the Bound column. Depending on how the ColumnWidth property is set, what is
*displayed* might be different from the value.

Furthermore, a RequestID field, although normally numeric, might be set as a
text field to accomodate leading zeros or alphabetic suffixes.

Let's assume your field is a text field. Try cutting and pasting the
following:

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

' This line contantenates the literal string [RequestID] =
' with the value of the combo box, placing single quotes
' around the combo box value. This is the way to handle
' string values
rs.FindFirst "[Request ID] = '" & Me![Combo24] & ""

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Set the combo box to a blank string
Me![Combo24] = ""

End Sub

If this doesn't work, the value must be a number. Cut and paste:

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

' Single quotes around the combo box value are not needed
' for a numeric value
rs.FindFirst "[Request ID] = " & Me![Combo24]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Set the combo box to a blank string
Me![Combo24] = ""

End Sub

Hope that helps.
Sprinks

Sprinks,

Ok still no luck, but am determined not to give it up as a bad job

The error message now is: Run time Error 3020, Update or cancelupdate
without addnew or edit. It has highlighted the bottom line i.e. just
Me![Combo24] = "" out of the whole thing as below

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Combo24] = ""

End Sub

Cheers,
Matt

Matt,

My apologies. I'd thought you'd added code to the end of your Rs.Findfirst
line. Return it to what it was, adding my new line at the end:

Private Sub Combo24_AfterUpdate()

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Request ID] = '" & Me![Combo24] = ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me![Combo24] = ""

End Sub

Hope that helps.

Sprinks

Sprinks,

Right ok thankyou.
Maybe i wont go into the VBA route as i am puzzled about it all, was mainly
showing it to you as you might understand where I was going wrong

From your procedure i get an error message with run time error 3077 about a
syntax error within the following expression:

rs.FindFirst "[Request ID] = '" & Me![Combo24]

That seems to be the only problem I believe and i am just not going to
bother going into it anymore after this

Many Thanks
Matt

:

Matt,

On Current is a Form event that is triggered when you move to another
record. But you don't need to use it--simply modify your AfterUpdate event
procedure:

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

Set rs = Me.Recordset.Clone

' Use the current value of the combo box to find the 1st matching record
rs.FindFirst "[Request ID] = '" & Me![Combo24]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Reset the combo box to a blank string
Me![Combo24] = ""

End Sub

Also, if you're planning to get into Access VBA programming, the first smart
thing to do is to name your form controls meaningfully, preferably per
established naming conventions which can be found in any good Access
reference.

Bound controls are normally named by the field to which they are Bound
prefaced by a three-character prefix that identifies the control type, e.g.,

txtLName
cboCustomerID
chkActive

or, in your case, cboRequestIDLookup

Following these conventions make your programming life *much* easier.
Should you choose to rename this particular control, unfortunately, Access
does not automatically rename your event procedure. To rectify, create a new
AfterUpdate event procedure for the renamed control, cut and paste the body
of the old procedure into the new one, and delete the old.

Hope that helps.

Sprinks


:

Sprinks,

As there is no option to tab On Current within properties of my combo box
should it be stored within Afer Update? Or should i be donig this within the
form properties?

My VBA currently reads as:
Private Sub Combo24_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

What alterations do i need to make?

Many Thanks,
Matt




:

Matt,

An unbound control is simply a container that displays data. It has the
same value for all records, or rather, it has a single value for the form,
and *displays* this value on all records.

When a control is Bound, Access attempts to save data entered into it to the
field to which it is Bound. Since, in your case, you'd entered a value that
already existed, which presumably is the primary key of your table, Access is
displaying a warning message.

Since the combo box is just meant as a lookup value and has no meaning once
you've reached the desired record, set it to Null or a blank string either at
the end of your code or in the On Current event, which triggers when you
arrive at a new record:

Me![YourComboBox] = ""

Hope that helps.
Sprinks


:

I have set up a form to search for a record in a combo box under field
Request ID. When the field is unbound i can carry out the search and find the
record but when i scroll through the records this Request ID remains even as
I am scrolling through. When the requestID is bound, it produces an error
message about duplicating the data!
Any help would be much appreciated
Matt