N
NEWER USER
I used some code from Allen B. and it works fine in Access 2K3. However, it
will not compile in 2K7. It fails at .Edit. Member or data object not
found. What might I do to change/alter to comply with 2K& standards? Any
help appreciated.
Function UpdateGroupAlias()
On Error GoTo UpdateGroupAlias_Err
Dim strErrMsg As String 'For Error Handling
Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim lngMakeID As Long
Dim bytMaxColumns As Byte
Dim pbytNumColumns As Byte
Dim db As Database
Dim rs As Recordset
strSQL = "Delete * from tblGroupAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.RunSQL "INSERT INTO tblGroupAlias ( MakeID, GroupID ) " & vbCrLf & _
"SELECT qryxCatalog1.CodeID, qryxCatalog1.GroupID " & vbCrLf & _
"FROM qryxCatalog1 " & vbCrLf & _
"ORDER BY qryxCatalog1.CodeID, qryxCatalog1.GroupID;"
DoCmd.SetWarnings True
bytMaxColumns = pbytNumColumns
Set db = CurrentDb
Set rs = db.OpenRecordset("tblGroupAlias") 'table used to redefine/alias
the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngMakeID = !MakeID
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !MakeID = lngMakeID
.Edit '*** Fails at this line ***
!Level = btLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With
UpdateGroupAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
UpdateGroupAlias_Err:
Select Case Err
Case Else
UpdateGroupAlias = Err.Number
Resume UpdateGroupAlias_Exit
End Select
End Function
will not compile in 2K7. It fails at .Edit. Member or data object not
found. What might I do to change/alter to comply with 2K& standards? Any
help appreciated.
Function UpdateGroupAlias()
On Error GoTo UpdateGroupAlias_Err
Dim strErrMsg As String 'For Error Handling
Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim lngMakeID As Long
Dim bytMaxColumns As Byte
Dim pbytNumColumns As Byte
Dim db As Database
Dim rs As Recordset
strSQL = "Delete * from tblGroupAlias"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.RunSQL "INSERT INTO tblGroupAlias ( MakeID, GroupID ) " & vbCrLf & _
"SELECT qryxCatalog1.CodeID, qryxCatalog1.GroupID " & vbCrLf & _
"FROM qryxCatalog1 " & vbCrLf & _
"ORDER BY qryxCatalog1.CodeID, qryxCatalog1.GroupID;"
DoCmd.SetWarnings True
bytMaxColumns = pbytNumColumns
Set db = CurrentDb
Set rs = db.OpenRecordset("tblGroupAlias") 'table used to redefine/alias
the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
lngMakeID = !MakeID
bytLevel = 0
intAlias = 65 'ascii value of 'A'
Do While !MakeID = lngMakeID
.Edit '*** Fails at this line ***
!Level = btLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias = 65 + bytMaxColumns Then
bytLevel = bytLevel + 1
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With
UpdateGroupAlias_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
UpdateGroupAlias_Err:
Select Case Err
Case Else
UpdateGroupAlias = Err.Number
Resume UpdateGroupAlias_Exit
End Select
End Function