Frustrating recordsource problem

[ColinWard]

Hi. I use two different pieces of code to manipulate a recordsource for
a form. The first one sets the recordsource to null when the form loads.
The second is supposed to display the corresponding record when a name
is selected from a combobox.

---Code start----

Private Sub Form_Load()

If Not Me.FilterOn Then 'If filter is on, then this form was opened from
another form by double cicking on a contact, so don't null the recordset

StrSql = "select * from Contacts where False" 'else null the
recordset so that form is blank at startup
Form.RecordSource = StrSql 'until goto box has selected
something
Else
cbxSelectContact.Enabled = False
End If
End Sub
---Code End-----

----Code Start----

Private Sub cbxSelectContact_AfterUpdate()
Form.RecordSource = "Contacts"
'Bind the form to the contacts table
'find the record in the table that matches the control
'display it in the form.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![cbxSelectContact],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

If UCase(streditmode) = "B" Then 'Browse mode, so lock the
appropriate controls!
If Not LockControlsForBrowse(Me) Then
DoCmd.CancelEvent 'Don't continue to open the form!
FormattedMsgBox ("An error occured while setting up this
form for Browse Mode.@ Please report this error to the Program
Administrator@")
End If
End If
End Sub

-----Code end-----
the second function is the one that doesnt work because when I select a
contact from the dropdwon, their info is not displayed. the thi ng is
tht this works on another form.

thanks

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Allen Browne]

Hi Colin

If you are reassigning the RecordSource of the form, why not just load one
record:

Private Sub cbxSelectContact_AfterUpdate()
Dim strSql As String
If Not IsNull(Me.cbxSelectContact) Then
If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If
'Reassign the recordsource
strSql = "SELECT * FROM Contacts WHERE ContactID = " &
Me.cbxSelectContact) & ";"
Me.RecordSource = strSql
End If
End Sub

(BTW, it may be more efficient to use Form_Open rather than Form_Load to
initially assign the RecordSource.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ColinWard" <je*********@hotmail.com> wrote in message
news:40*********************@news.frii.net...

Hi. I use two different pieces of code to manipulate a recordsource for
a form. The first one sets the recordsource to null when the form loads.
The second is supposed to display the corresponding record when a name
is selected from a combobox.

---Code start----

Private Sub Form_Load()

If Not Me.FilterOn Then 'If filter is on, then this form was opened from
another form by double cicking on a contact, so don't null the recordset

StrSql = "select * from Contacts where False" 'else null the
recordset so that form is blank at startup
Form.RecordSource = StrSql 'until goto box has selected
something
Else
cbxSelectContact.Enabled = False
End If
End Sub
---Code End-----

----Code Start----

Private Sub cbxSelectContact_AfterUpdate()
Form.RecordSource = "Contacts"
'Bind the form to the contacts table
'find the record in the table that matches the control
'display it in the form.

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Nz(Me![cbxSelectContact],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

If UCase(streditmode) = "B" Then 'Browse mode, so lock the
appropriate controls!
If Not LockControlsForBrowse(Me) Then
DoCmd.CancelEvent 'Don't continue to open the form!
FormattedMsgBox ("An error occured while setting up this
form for Browse Mode.@ Please report this error to the Program
Administrator@")
End If
End If
End Sub

-----Code end-----
the second function is the one that doesnt work because when I select a
contact from the dropdwon, their info is not displayed. the thi ng is
tht this works on another form.

thanks

Colin

[ColinWard]

Hi Allen.

thanks for your help. Unfortunately, it still does not work. The problem
is that for some reason the recordsource is not being updated. When I
put a breakpoint at the spot where it should reassign the
recordsource(cbxselectContact_AfterUpdate)I still see the old SQL (i.e.
strSql = "Select from contacts where false)
thanks Allen

Colin

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Hi again Allen,

I ahve tracked the problem down to the fact that your code does not pass
the contact ID to the form. I know this because if I open the form in
Edit mode and select someone from the dropdown(cbxselectContact) I get a
dialog asking for the contact ID. I input a number of a person and I get
a record appearing. The recordsource does indeed update to the new one.
but it does it in the OnCurrent event. I expected it to do in the
assignment statement itself.

thanks again for your help

Colin


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[ColinWard]

Hi Allen.

I finally got it to work.

Heres the code.

the trick was adding .Value after the cbxselectcontact in the query.

----Code Start----

Private Sub cbxSelectContact_AfterUpdate()
Dim strSql As String
If Not IsNull(Me.cbxSelectContact) Then
If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If
'Reassign the recordsource
strSql = ""
strSql = "SELECT * FROM Contacts WHERE ContactID = "
strSql = strSql & cbxSelectContact.Value
Me.RecordSource = strSql
End If
End Sub
---End Code---

Thanks Again Allen

Colin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!