Form code debugging

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a form that basically just references and underlying table with some added functionality.

The form opens. the focus is set to a field that 'must' be entered. If it is not entered it will not proceed until the user enters a 'unique record'. It then verifies the entered record is 'unique'. then continues on the the other fields
I added ADD and DELETE buttons and a list box for the record index, and turned off the visible of the bottom record navigator
After the user adds an acceptable new first entry, the list box is supposed to update and highlight the new entry

Here are the problems I cannot get past
1. After the user adds a new record, I can't get the list box to highlight the new entry. ( I do get it in the list box with the .update method
2. After the user adds a new record, the bookmark method in the 'list7' list box gives me an error

version: AccessX

I have (3) different access books I referenced to get this far and a couple very useful replies answered on this ng

At this point, my candle is not shining much light on the dark path ahead.
Any Guidance would be greatly, tremendously appreciated.

Robert Goo
(novice VB programmer

COD
---------------------------------------------------------------------
Option Compare Databas

Private Sub Form_Load(
Me![SYM].SetFocus 'set focus to first fiel
End Su

Private Sub List7_AfterUpdate(
' Find the record that matches the control
Dim rs As Objec

Set rs = Me.Recordset.Clon
rs.FindFirst "[SYM] = '" & Me![List7] & "'
If Not rs.EOF Then Me.Bookmark = rs.Bookmark '<<<This is where I get my error



End Su

Private Sub SYM_BeforeUpdate(Cancel As Integer

Dim rs As ADODB.Recordse
Dim lngrecordnum As Lon

Set rs = New ADODB.Recordse
rs.ActiveConnection = CurrentProject.Connectio
rs.CursorType = adOpenStati
rs.LockType = adLockOptimisti
rs.CursorLocation = adUseServe

rs.Open "Select * from tblMasterPlantList " &
"WHERE SYM = " & ReplaceApostrophe([SYM]),
Options:=adCmdTex


If Not rs.EOF The

MsgBox "You entered a Symbol that already exists, " & vbCrLf &
"Please Change your Symbol Abbreviation!

'this ensures the focus stays on current fiel
Cancel = Tru

' If sym is not in the current table, then add it
Els


'force an add to get the list box to add value of field to the list box content
With r
.AddNe
.Fields!SYM = Me.SY
.Updat
End Wit

'!!looking for a way to highlight the newly entered symbol in the listbox '<<<<
lngrecordnum = Me.Form.CurrentRecor
Me![List7].Requer

'after the requery, I think I need to re-find the selected numbe
Me![List7].Selected(lngrecordnum) = Tru


End I



'set to nothin
rs.Clos
Set rs = Nothin

End Su
 
I can't get the list box to highlight the new entry.

Try .Refresh or .Requery method on the list box.
the bookmark method in the 'list7' list box gives me an error
Click to expand...

Recordset bookmarks are invalidated after the underlying recordset has
been changed. .Requery should fix this, but you will not be able to
re-use the old bookmark. You will need to repeat the search that gave
you the bookmark in the first place.

Pavel

Robert said:
I created a form that basically just references and underlying table with some added functionality.

The form opens. the focus is set to a field that 'must' be entered. If it is not entered it will not proceed until the user enters a 'unique record'. It then verifies the entered record is 'unique'. then continues on the the other fields.
I added ADD and DELETE buttons and a list box for the record index, and turned off the visible of the bottom record navigator.
After the user adds an acceptable new first entry, the list box is supposed to update and highlight the new entry.

Here are the problems I cannot get past:
1. After the user adds a new record, I can't get the list box to highlight the new entry. ( I do get it in the list box with the .update method)
2. After the user adds a new record, the bookmark method in the 'list7' list box gives me an error.

version: AccessXP

I have (3) different access books I referenced to get this far and a couple very useful replies answered on this ng.

At this point, my candle is not shining much light on the dark path ahead.
Any Guidance would be greatly, tremendously appreciated.

Robert Good
(novice VB programmer)

CODE
----------------------------------------------------------------------
Option Compare Database

Private Sub Form_Load()
Me![SYM].SetFocus 'set focus to first field
End Sub

Private Sub List7_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SYM] = '" & Me![List7] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark '<<<This is where I get my error.



End Sub

Private Sub SYM_BeforeUpdate(Cancel As Integer)

Dim rs As ADODB.Recordset
Dim lngrecordnum As Long

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenStatic
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseServer

rs.Open "Select * from tblMasterPlantList " & _
"WHERE SYM = " & ReplaceApostrophe([SYM]), _
Options:=adCmdText


If Not rs.EOF Then

MsgBox "You entered a Symbol that already exists, " & vbCrLf & _
"Please Change your Symbol Abbreviation!"

'this ensures the focus stays on current field
Cancel = True

' If sym is not in the current table, then add it.
Else


'force an add to get the list box to add value of field to the list box contents
With rs
.AddNew
.Fields!SYM = Me.SYM
.Update
End With

'!!looking for a way to highlight the newly entered symbol in the listbox '<<<<<
lngrecordnum = Me.Form.CurrentRecord
Me![List7].Requery

'after the requery, I think I need to re-find the selected number
Me![List7].Selected(lngrecordnum) = True


End If



'set to nothing
rs.Close
Set rs = Nothing

End Sub
Click to expand...
 
Thanks Pavel
Sorry for not thanking you sooner. I got caught up on another projects deadline

Thanks again

Robert Good
 
Back
Top