adding new records to SQL from Access '97 front en

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

adding new records to SQL from Access '97 front en

Author

Topic

pmehta62
Starting Member

2 Posts

Posted - 2004-08-16 : 11:30:21

Hi all -

I am new to this board, and would really appreciate your help with my problem. I am currently transferring the backend of access to an sql server to store my records, they will however be viewed from access.

I have replicated the tables in access into SQL, the data has successfully migrated, but i am having problems in adding a new record to the population. when i hit new record in access, it takes me to the last record in the database instead of showing a blank form. I have no idea how to resolve this. The following is the code i have used to execute adding a new record: (pls note 'mstMasterList' is the name of the table in SQL for which i am trying to add new records to. thanks in advance for your help!

thanks,

Paras

Private Sub cmdAddClientToMasterList_Click()
On Error GoTo cmdAddClientToMasterList_Err
'Purpose: This procedure will add a client to the database

Dim nMasterListID As Variant
Dim sSQL As String

If Not IsFilled(Me.cmbUsers) Then
MsgBox "Please identify yourself. Operation cancelled."
Else
'Prompt the user for a password
If CorrectPassword Then
'Add client to master list table (mstMasterList)
sSQL = "INSERT INTO mstMasterList (mstLegalEntity,mstFileCreatedBy, mstFileCreatedOn) "
sSQL = sSQL & " SELECT " & """" & "New_Client" & """" & " As Name, " & _
"""" & Nz(Me.cmbUsers) & """" & "AS User, " & _
"#" & Now() & "#" & "AS CurrentDate"

CurrentDb.Execute (sSQL)

'Determine Client ID in the MasterList table
nMasterListID = DMax("[mstMasterListID]", "mstMasterList")

If Not IsNull(nMasterListID) Then
'Add client to the answers table (ansAnswers)
CurrentDb.Execute ("INSERT INTO ansAnswers (ansMasterListID) VALUES (" & nMasterListID & ")")

'Open the Master list form
DoCmd.OpenForm "MasterList_frm", acNormal, , , , , nMasterListID
End If
End If
End If

cmdAddClientToMasterList_Ext:
Exit Sub

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-08-16 : 15:08:55

Check to see if you have a key field on this table.
You cannot add records Via Access without a key field.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources