Access Access to SQL migration, Auto number in form error

May 18, 2010
Reaction score
We have migrated a test of our database to SQL to work the bugs out prior to go live. The migration has caused some problems with things that worked fine in Access.

I have a table named WI FUNDS which pulls customer information from the CUSTOMER table in form view. When creating a new record in form view for WI FUNDS table we would enter the Customer number and it would auto populate the Name, Address, etc. When I migrated the tables it would not let me add a new record so I created a new form with the same data that would allow new records but it will not let me type in the Customer number. I get " Control can't be edited; its bound to AutoNumber field 'CustomerID'". My form does alot of things so I have posted the whole code here. Any ideas would be greatly appreciated.

Option Compare Database
Option Explicit

Private Sub Abbr_Change()
Dim i As Variant
i = Me.Abbr.ListIndex
Me.Town.Value = Me.Abbr.Column(2, i)
Me.Range.Value = Me.Abbr.Column(3, i)

End Sub

Private Sub Command99_Click()

On Error GoTo Err_Command99_Click

Dim stDocName As String
Dim stLinkCriteria As String


stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit Sub

MsgBox Err.Description
Resume Exit_Command99_Click

End Sub

Private Sub CustomerID_AfterUpdate()

Dim strFilter As String
strFilter = "CustomerID = " & Me!CustomerID
Me!Customeraddress = DLookup("MailingAddress", "customers table", strFilter)
Me!Customercity = DLookup("city", "customers table", strFilter)
Me!Customerstate = DLookup("state", "customers table", strFilter)
Me!Customerzip = DLookup("zip", "customers table", strFilter)

End Sub

Private Sub FeeCatagory_AfterUpdate()
Dim strFilter As String
strFilter = "ID = " & Me!FeeCatagory
Me![WI Fund Table.FeeAmount] = DLookup("feeamount", "fee table", strFilter)

End Sub

Private Sub Form_Current()

End Sub

Private Sub Form_Load()

End Sub

Private Sub Parcel__BeforeUpdate(Cancel As Integer)
Dim wrkJet As Workspace
Dim dbsParcels As Database

'Set wrkJet = CreateWorkspace("NewJetWorkspace", _
"admin", "", dbUseJet)
'Set dbsParcels = wrkJet.OpenDatabase("m:\Richland County Zoning Database_2002.mdb")
Set dbsParcels = CurrentDb()
Dim rsparcels As Recordset
With dbsParcels
Set rsparcels = .OpenRecordset("parcel table", _

Dim wantsave As Integer
Dim blnfound As Boolean
blnfound = False

Do Until rsparcels.EOF
If Me.Parcel_ = rsparcels!Parcel Then
blnfound = True
Exit Do
End If

If blnfound = True Then
MsgBox "Parcel found"
wantsave = MsgBox("Do you want to add parcel " & Me.Parcel_, vbYesNo)
If wantsave = 6 Then
rsparcels!Parcel = Me.Parcel_


End If
End If
End With
End Sub

Private Sub SanitaryFormOpenButton_Click()
On Error GoTo Err_SanitaryFormOpenButton_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim strArgs As String
strArgs = "newSPfromWI|" & Me!CustomerID.Value & "|" & _
Me![Parcel#].Value & "|" & Me!Township.Column(0) & "|" & _
Me!Township.Column(2) & "|" & Me!Township.Column(3) & "|" & _
Me!Qtr.Value & "|" & Me!Qtr2.Value & "|" & _
Me![locationaddress#].Value & "|" & Me!LocationRoadName.Value & "|" & _
Me!Bedrooms.Value & "|" & Me!SanitaryPermitAppDate.Value & "|" & _
'stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , , , , strArgs
Exit Sub

MsgBox Err.Description
Resume Exit_SanitaryFormOpenButton_Click

End Sub

Private Sub customerzip_Change()
Dim i As Variant
i = Me.Customerzip.ListIndex
Me.Customercity.Value = Me.Customerzip.Column(1, i)
Me.Customerstate.Value = Me.Customerzip.Column(2, i)

End Sub

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

Need help in printing vba code 0
Access Can't Get Dlookup To Work 1
Access DLOOKUP with IF Function in MS ACCESS DB 0
Access Access DLookup Function – more than 1 criteria 0
Access Choosing Printer VBA Access 0
Access Open Form with WhereCondition 6
Access Access Eliminate Blank Query Results 1
Access VBA in Access confusion. 1