Adding extra information to existing data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created an Access database that is linked via ODBC to a couple of
tables from our proprietory SQL Server based business system.

I want to create a form to view certain Job data from the linked SQL Server
tables and record some related data in a separate Access table.

To begin with, I have set my form's control source to a Select query on the
SQL Server data. On the form, I have placed a combo-box control whose own
data source is another Select query returning a list of all the existing Job
numbers from the SQL Server data. I then coded the AfterUpdate event handler
for the combo-box as follows:

Private Sub order_no_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[order_no] = '" & Me![order_no] & "'"
Me.Bookmark = rs.Bookmark
Me![Order] = Me![order_no]
Set rs = Nothing
End Sub

.... where Me![Order] is a separate text box bound to the Job number in the
form's data source.

Instead of populating the form with the relevant data, though, I am getting
this error message:

"Run time error '3146':

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key
row in object 'opheadm' with unique index 'i_176282674x'.
(#2601)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated. (#3621).

Can anyone suggestwhat I might be doing wrong here?

TIA

Vaughan
 
Hi Dan

Thanks for replying. The combo-box is unbound.

Vaughan



Dan Artuso said:
Hi,
Is you combo box bound? It should be unbound.

--
HTH
Dan Artuso, Access MVP


Vaughan said:
I have created an Access database that is linked via ODBC to a couple of
tables from our proprietory SQL Server based business system.

I want to create a form to view certain Job data from the linked SQL Server
tables and record some related data in a separate Access table.

To begin with, I have set my form's control source to a Select query on the
SQL Server data. On the form, I have placed a combo-box control whose own
data source is another Select query returning a list of all the existing Job
numbers from the SQL Server data. I then coded the AfterUpdate event handler
for the combo-box as follows:

Private Sub order_no_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[order_no] = '" & Me![order_no] & "'"
Me.Bookmark = rs.Bookmark
Me![Order] = Me![order_no]
Set rs = Nothing
End Sub

... where Me![Order] is a separate text box bound to the Job number in the
form's data source.

Instead of populating the form with the relevant data, though, I am getting
this error message:

"Run time error '3146':

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key
row in object 'opheadm' with unique index 'i_176282674x'.
(#2601)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated. (#3621).

Can anyone suggestwhat I might be doing wrong here?

TIA

Vaughan
Click to expand...
Click to expand...
 
To clarify, the combo-box's row source is a Select Query on the linked SQL
Server table. The combo-box itself is unbound.

Vaughan said:
Hi Dan

Thanks for replying. The combo-box is unbound.

Vaughan



Dan Artuso said:
Hi,
Is you combo box bound? It should be unbound.

--
HTH
Dan Artuso, Access MVP


Vaughan said:
I have created an Access database that is linked via ODBC to a couple of
tables from our proprietory SQL Server based business system.

I want to create a form to view certain Job data from the linked SQL Server
tables and record some related data in a separate Access table.

To begin with, I have set my form's control source to a Select query on the
SQL Server data. On the form, I have placed a combo-box control whose own
data source is another Select query returning a list of all the existing Job
numbers from the SQL Server data. I then coded the AfterUpdate event handler
for the combo-box as follows:

Private Sub order_no_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[order_no] = '" & Me![order_no] & "'"
Me.Bookmark = rs.Bookmark
Me![Order] = Me![order_no]
Set rs = Nothing
End Sub

... where Me![Order] is a separate text box bound to the Job number in the
form's data source.

Instead of populating the form with the relevant data, though, I am getting
this error message:

"Run time error '3146':

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key
row in object 'opheadm' with unique index 'i_176282674x'.
(#2601)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated. (#3621).

Can anyone suggestwhat I might be doing wrong here?

TIA

Vaughan
Click to expand...
Click to expand...
Click to expand...
 
Hi,
Just noticed you're trying to set the job number.
That is probably where the duplicate key error is coming from.

--
HTH
Dan Artuso, Access MVP


Vaughan said:
To clarify, the combo-box's row source is a Select Query on the linked SQL
Server table. The combo-box itself is unbound.

Vaughan said:
Hi Dan

Thanks for replying. The combo-box is unbound.

Vaughan



Dan Artuso said:
Hi,
Is you combo box bound? It should be unbound.

--
HTH
Dan Artuso, Access MVP


I have created an Access database that is linked via ODBC to a couple of
tables from our proprietory SQL Server based business system.

I want to create a form to view certain Job data from the linked SQL Server
tables and record some related data in a separate Access table.

To begin with, I have set my form's control source to a Select query on the
SQL Server data. On the form, I have placed a combo-box control whose own
data source is another Select query returning a list of all the existing Job
numbers from the SQL Server data. I then coded the AfterUpdate event handler
for the combo-box as follows:

Private Sub order_no_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[order_no] = '" & Me![order_no] & "'"
Me.Bookmark = rs.Bookmark
Me![Order] = Me![order_no]
Set rs = Nothing
End Sub

... where Me![Order] is a separate text box bound to the Job number in the
form's data source.

Instead of populating the form with the relevant data, though, I am getting
this error message:

"Run time error '3146':

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key
row in object 'opheadm' with unique index 'i_176282674x'.
(#2601)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated. (#3621).

Can anyone suggestwhat I might be doing wrong here?

TIA

Vaughan
Click to expand...
Click to expand...
Click to expand...
 
Hmm. Do you mean the line that goes

Me![Order] = Me![Order_no] ?

I'll try taking it out ...
..
..
..
Hallelujah!

Many thanks Dan

Vaughan

Dan Artuso said:
Hi,
Just noticed you're trying to set the job number.
That is probably where the duplicate key error is coming from.

--
HTH
Dan Artuso, Access MVP


Vaughan said:
To clarify, the combo-box's row source is a Select Query on the linked SQL
Server table. The combo-box itself is unbound.

Vaughan said:
Hi Dan

Thanks for replying. The combo-box is unbound.

Vaughan



:

Hi,
Is you combo box bound? It should be unbound.

--
HTH
Dan Artuso, Access MVP


I have created an Access database that is linked via ODBC to a couple of
tables from our proprietory SQL Server based business system.

I want to create a form to view certain Job data from the linked SQL Server
tables and record some related data in a separate Access table.

To begin with, I have set my form's control source to a Select query on the
SQL Server data. On the form, I have placed a combo-box control whose own
data source is another Select query returning a list of all the existing Job
numbers from the SQL Server data. I then coded the AfterUpdate event handler
for the combo-box as follows:

Private Sub order_no_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[order_no] = '" & Me![order_no] & "'"
Me.Bookmark = rs.Bookmark
Me![Order] = Me![order_no]
Set rs = Nothing
End Sub

... where Me![Order] is a separate text box bound to the Job number in the
form's data source.

Instead of populating the form with the relevant data, though, I am getting
this error message:

"Run time error '3146':

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert duplicate key
row in object 'opheadm' with unique index 'i_176282674x'.
(#2601)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated. (#3621).

Can anyone suggestwhat I might be doing wrong here?

TIA

Vaughan
Click to expand...
Click to expand...
Click to expand...
 
Back
Top