List Box Selected Item

[Guest]

First time post-er, novice VB Programme
experienced with lisp (autodesk) and Lingo (macromedia

boy vb is different...

version: Access X

On to the question

I have a form with many fields listing the current record of a table. The form has a list box referencing the 'key' item for each record in the table. I am using bookmark to change the current record set by selecting an item in the list box
The problem I am having is reversing the scenario. When the user adds a new record. The first field is brought in focus and must be entered. I am using the AfterUpdate method on the 'SYM' field to requery the list box and highlight the previously just entered symbol

The problem 'I think' is the requery is working but the new 'SYM' entry hasn't actually been put in the table yet, so its not part of the requery
How can I force an entry so the list box can do its job

I'm posting my code for the form

Private Sub Form_AfterInsert(
Me![List7].Requer
End Su

Private Sub Form_Load(
Me![SYM].SetFocu
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.Bookmar
End Su

Private Sub SYM_AfterUpdate(
Dim lngrecordnum As Lon

'!!!!looking for a way to highlight the newly entered symbol in the listbo
lngrecordnum = Me.Form.CurrentRecor
Me![List7].Requer
Me![List7].Selected(lngrecordnum) = Tru
End Su

Private Sub SYM_BeforeUpdate(Cancel As Integer

Dim rs As ADODB.Recordse

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

End I

'set to nothin
rs.Clos
Set rs = Nothin

End Su

Public Function ReplaceApostrophe(strSymbol As String) As Strin
'Surround text with apostrophes and replace any apostrophies i
'the string with two apostrophe
ReplaceApostrophe = "'" &
Replace(strSymbol, "'", "''") & "'
End Functio

Private Sub btnAddRecord_Click(
On Error GoTo Err_btnAddRecord_Clic

DoCmd.GoToRecord , , acNewRe
Me![SYM].SetFocu

Exit_btnAddRecord_Click
Exit Su

Err_btnAddRecord_Click
MsgBox Err.Descriptio
Resume Exit_btnAddRecord_Clic

End Su
Private Sub btnDeleteRecord_Click(
On Error GoTo Err_btnDeleteRecord_Clic

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer7
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer7

Me![List7].Requer

Exit_btnDeleteRecord_Click
Exit Su

Err_btnDeleteRecord_Click
MsgBox Err.Descriptio
Resume Exit_btnDeleteRecord_Clic

End Su
Private Sub btnOK_Click(
On Error GoTo Err_btnOK_Clic

DoCmd.Clos

Exit_btnOK_Click
Exit Su

Err_btnOK_Click
MsgBox Err.Descriptio
Resume Exit_btnOK_Clic

End Su

Robert Good

 

[Katrina]

You have to force a save of the new record before it will show up in the
requery of the list box.
try putting a save record (docmd.runcommand accmdsaverecord) in the after
update of the SYM field before you requery the list box. beware that if
other fields are required in order for the record to be saved, you will get
an error message!

HTH
Kat

First time post-er, novice VB Programmer
experienced with lisp (autodesk) and Lingo (macromedia)

boy vb is different....

version: Access XP

On to the question:

I have a form with many fields listing the current record of a table. The

form has a list box referencing the 'key' item for each record in the table.
I am using bookmark to change the current record set by selecting an item in
the list box.

The problem I am having is reversing the scenario. When the user adds a

new record. The first field is brought in focus and must be entered. I am
using the AfterUpdate method on the 'SYM' field to requery the list box and
highlight the previously just entered symbol.

The problem 'I think' is the requery is working but the new 'SYM' entry

hasn't actually been put in the table yet, so its not part of the requery.

How can I force an entry so the list box can do its job.

I'm posting my code for the form:

Private Sub Form_AfterInsert()
Me![List7].Requery
End Sub

Private Sub Form_Load()
Me![SYM].SetFocus
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
End Sub


Private Sub SYM_AfterUpdate()
Dim lngrecordnum As Long

'!!!!looking for a way to highlight the newly entered symbol in the listbox
lngrecordnum = Me.Form.CurrentRecord
Me![List7].Requery
Me![List7].Selected(lngrecordnum) = True
End Sub

Private Sub SYM_BeforeUpdate(Cancel As Integer)

Dim rs As ADODB.Recordset

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

End If

'set to nothing
rs.Close
Set rs = Nothing

End Sub


Public Function ReplaceApostrophe(strSymbol As String) As String
'Surround text with apostrophes and replace any apostrophies in
'the string with two apostrophes
ReplaceApostrophe = "'" & _
Replace(strSymbol, "'", "''") & "'"
End Function


Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click


DoCmd.GoToRecord , , acNewRec
Me![SYM].SetFocus

Exit_btnAddRecord_Click:
Exit Sub

Err_btnAddRecord_Click:
MsgBox Err.Description
Resume Exit_btnAddRecord_Click

End Sub
Private Sub btnDeleteRecord_Click()
On Error GoTo Err_btnDeleteRecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Me![List7].Requery

Exit_btnDeleteRecord_Click:
Exit Sub

Err_btnDeleteRecord_Click:
MsgBox Err.Description
Resume Exit_btnDeleteRecord_Click

End Sub
Private Sub btnOK_Click()
On Error GoTo Err_btnOK_Click


DoCmd.Close

Exit_btnOK_Click:
Exit Sub

Err_btnOK_Click:
MsgBox Err.Description
Resume Exit_btnOK_Click

End Sub

Robert Good.

 

[Guest]

Robert

Since you are using ADO in the before update event, if the key is not a duplicate, just add it to the recordset

with r
.addne
.field(fieldname)=valu
.updat
end wit

This will add the record without having to save the form. Then you can do your requery

 

[Katrina]

Try this maybe?

with rs
.addnew
![SYM] = me![SYM]
.update
end with

BTW, when you try it the other way are you getting an error message? If so
what is it?

HTH
Kat

Thanks Katrina and Brandon for the responses.

I am tinkering with brandons code

'force an add to get the list box to add value of field to the list box contents
With rs
.AddNew
.Field(SYM) = [SYM] <<<<<< OR
.field([SYM]) = [SYM]<<<<<<<<<OR
.field(SYM)=[SYM]<<<<<<<<<
.Update
End With

The tables field name is SYM and the textbox field is also named SYM
I am not having any luck with the above statement.
As I mentioned earlier, I am a novice.

Thanks again.


----- Brandon wrote: -----

Robert,

Since you are using ADO in the before update event, if the key is not

a duplicate, just add it to the recordset.