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
stDocName = "A - SANITARY PERMITS"
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command99_Click:
Exit Sub
Err_Command99_Click:
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", _
dbOpenDynaset)
Dim wantsave As Integer
Dim blnfound As Boolean
rsparcels.MoveFirst
blnfound = False
Do Until rsparcels.EOF
If Me.Parcel_ = rsparcels!Parcel Then
blnfound = True
Exit Do
End If
rsparcels.MoveNext
Loop
If blnfound = True Then
MsgBox "Parcel found"
Else
wantsave = MsgBox("Do you want to add parcel " & Me.Parcel_, vbYesNo)
If wantsave = 6 Then
rsparcels.MoveFirst
rsparcels.AddNew
rsparcels!Parcel = Me.Parcel_
rsparcels.Update
Else
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
stDocName = "A - SANITARY PERMITS"
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 & "|" & _
Me!OriginalDateofInstallation.Value
'stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , , , , strArgs
Exit_SanitaryFormOpenButton_Click:
Exit Sub
Err_SanitaryFormOpenButton_Click:
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
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
stDocName = "A - SANITARY PERMITS"
stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command99_Click:
Exit Sub
Err_Command99_Click:
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", _
dbOpenDynaset)
Dim wantsave As Integer
Dim blnfound As Boolean
rsparcels.MoveFirst
blnfound = False
Do Until rsparcels.EOF
If Me.Parcel_ = rsparcels!Parcel Then
blnfound = True
Exit Do
End If
rsparcels.MoveNext
Loop
If blnfound = True Then
MsgBox "Parcel found"
Else
wantsave = MsgBox("Do you want to add parcel " & Me.Parcel_, vbYesNo)
If wantsave = 6 Then
rsparcels.MoveFirst
rsparcels.AddNew
rsparcels!Parcel = Me.Parcel_
rsparcels.Update
Else
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
stDocName = "A - SANITARY PERMITS"
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 & "|" & _
Me!OriginalDateofInstallation.Value
'stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , , , , strArgs
Exit_SanitaryFormOpenButton_Click:
Exit Sub
Err_SanitaryFormOpenButton_Click:
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