|
Subject:
|
Add record
|
|
Posted By:
|
mateenmohd
|
Post Date:
|
10/3/2006 3:21:50 AM
|
I have to make add new record button at access form.
table use sql server 2000 with odbc connection.
I make button by toolbar command button
when I click the button it give error message that
"you can't goto specified record"
what coding will use in button on click event ?
what change in properties ?
Second problem
--------------
I have a table with fied is ID.
I make data entry form.
I want that ID field should be auto insert record.
i.e. when insert the record ID field auto insert the
next number ?
ie 1, 2, 3, .......
what changing in table field / form properties ?
regards
Mateen
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
10/3/2006 6:53:18 AM
|
For the first issue, I use code something like this, taking values from the form:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim stUID As String
Dim stPWD As String
Dim sValue1 As String
Dim sValue2 As String
stUID = "User"
stPWD = "********"
sValue1 = Me.txtTextControl
sValue2 = Me.SomeOtherControl
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cnn.Open "DSN=WPM;", stUID, stPWD
rs.CursorLocation = 3
rs.Open "SELECT * FROM tblSQLTable", cnn, 3, 3
rs.AddNew
rs("FieldName1") = sValue1
rs("FieldName2") = sValue2
...
rs.Update
rs.Close
cnn.Close
As to the second issue, I generally let SQL supply the autonumber by setting the PK field to Identity, and giving it a starting number 1, seed 1. If you can't do this, and you are not worried about errors, then you can create a view on the PK of the table, and set it to order DESC, and SELECT TOP 1. Then take that value and add one to it before updating the table. I would use a view instead of a pass-through query since SQL will optimize the view, but not the query.
Did that help?
mmcdonal
|
