Adding extra information to existing data

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
 
G

Guest

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...
 
G

Guest

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...
 
D

Dan Artuso

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy Record Problem 3
VBA Excel data to SQL Sever table 1
Not able to select value from combo box. 2
Combo Box Problem 5
form won't update, what to check 2
Setting a combo Rowsource from SQL server 2
Data Validation on Form linked to SQL table 3
Export Filtered Form Data To Excel 4

Top