Form_Load

[JezLisle]

On my database my main Input Forms open with a Blank Record and I can either Add New or Search existing records. My problem is when I click on Search Existing Records. It is supposed to open a new form with a ListBox adnd populate the ListBox with the SQL data

I keep getting a Run-time error -2147467259
Method 'Recordset' of Object _Listbox' failed.

what does this mean? Where have I gone wrong?

My DB is Access FE & SQL Server BE.

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
        cnn.Open "Provider=sqloledb;Data Source=C****1;Initial Catalog=*******;Integrated Security=SSPI;"
'*************************************************************************************
    DoCmd.SetWarnings False
    Me.lstSearch.Requery
    Call UnLockAll
'****************************************************************************
        sQRY = _
            "SELECT [Name], DateOfBirth, PatientRef  " & _
            "FROM jez.IC_ReferralRecord " & _
            "WHERE InputBy <> 'DONOTDELETE' " & _
        "ORDER BY DateOfBirth, [Name] DESC "
    rs.CursorLocation = adUseServer
    rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
    rs.Close
'****************************************************************************
    Set lstSearch.Recordset = rs
    Me.RecordSource = sQRY
    Me.lstSearch.RowSource = sQRY
    Me.lstSearch.Requery
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

 

[DCrake]

The command you want is RecordSource not Recordset.

 

[JezLisle]

I have changed Recordset to RecordSource, and get compile error - Method or data member not found.

 

[DCrake]

i) Set lstSearch.Recordset = rs
ii) Me.RecordSource = sQRY
iii) Me.lstSearch.RowSource = sQRY
iv) Me.lstSearch.Requery


i) A list box does not have a .Recordset

ii) means that you are setting the record source of the current open forms to the one generated with sQRY


iii) this is referring to a listbox on the current form

iv) simple list box requery


You have not shown anywhere where you are opening the form to display the listbox.

Such As DoCmd.OpenForm ....

What you may need to do is to add a new textbox to your calling form and store the sQRY string to it. Make that control invisible. Then when you open the filter form on the OnLoad event state

Me.ListBoxName.RowSource = Forms("CallingForm")("ControlName")

 

[JezLisle]

I have this code below to load the Form from

Private Sub cmdSearchRecords_Click()
    DoCmd.OpenForm "frmSearchRecords"
End Sub

This comes a CommandButton on my main form.

Can you explain on this as not sure I understand properly

What you may need to do is to add a new textbox to your calling form and store the sQRY string to it. Make that control invisible. Then when you open the filter form on the OnLoad event state

 

[DCrake]

The way you have described your situation, and correct me if I am wrong, is that you want to open an unbound form (frmSearchRecords) that will contain a list box control which will be populated with the contents of the recordset generated on the initial form (Calling form).

You have a button on the calling form that open the frmSearchRecords.

You need somehow to pass the information from the calling form to the frmSearchRecords from about which records you want to display in the list box. My solution was to have a hidden control on the calling form that held the sQRY sql statement. And when the form opened it simply looked at the said control on the calling form to read in the sql string into the rowsource of the list box.

Another way would be to declare the string variable sQRY publically in a module

Public sQRY As String

Then when the frmSearchRecords form Opens you can state

Me.ListBoxName.RowSource = sQRY

 

[JezLisle]

Ok, yes I have an unbound form (frmSearchRecords) that has a ListBox on and is populated via SQL from my main table. The main table is populated via an inputform. On this InputForm I have a CommandButton which will open frmSearchRecords. All I want to do is when this form is open to run the SQL and bring back the results of the query in the ListBox.
I have done this before, between Access FE & BE but now I'm using SQL Server as the BE it doesn’t quite work.

 

[DCrake]

Can you actually talk to the SQL server? can you retrieve data at any point. Are you using DNS (less) connections?

David

 

[JezLisle]

Yes, the main form I use (frmInputData) uses this code below on open and it works fine. It just when I load this Search Form up it doesnt populate anything.

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
        cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"
'*************************************************************************************
    DoCmd.SetWarnings False
    strUser = fOSUserName()
    Call LockAll
    Me.cboGender.Enabled = False
    Me.cboReferredFrom.Enabled = False
    Me.cboReferralDestination.Enabled = False
    Me.cboReasonNotAccepted.Enabled = False
    Me.cboChangedDestination.Enabled = False
    Me.cmdAddNew.Enabled = True
    Me.cmdSubmit.Enabled = False
    Me.cmdSearchRecords.Enabled = True
    Me.cmdMainMenu.Enabled = True
'****************************************************************************
        sQRY = _
            "SELECT PatientID, PatientRef, Forename, Surname, [Name], Address1, Address2, Address3, " & _
            "PostCode, DateOfBirth, Age, Gender, ReceivedDate, ReceivedTime, ReferringAgent, " & _
            "ReferralDestination, Comments, ReasonNotAccepted, ChangedDestination, " & _
            "ChangedDestinationComments, ChangedInputBy, ReferralsFrom," & _
            "ChangedInputDate, InputBy, InputDate " & _
            "FROM jez.IC_ReferralRecord " & _
            "WHERE InputBy = 'DONOTDELETE' "
    cnn.Execute sQRY
'****************************************************************************
            sQRY = "INSERT INTO jez.IC_UserLog ([User], [Action], [DateofAction]) " & _
                        "VALUES ('" & strUser & "', 'Referral Record Input Form - Open Form', " & fProperDate(VBA.Now) & ")"
    cnn.Execute sQRY
'****************************************************************************
    cnn.Close
    Set cnn = Nothing
    DoCmd.SetWarnings False
    Me.txtDummy.SetFocus
End Sub

 

[DCrake]

Yes I can see that but what is happening in the search form. Does the list box have a rowsource? Are you attempting anywhere to pass a rowsource to your search form?

 

[Banana]

Just to point some things out:

1) In Access 2003, Listbox and Combobox does have a Recordset property, and either ADO or DAO recordset can be set to a listbox or combobox instead of using a Recordsource (e.g. merely passing the string). I *think* Access 2000 allows you to set recordsets to listboxes and comboboxes as well, but am not 100% sure about that. 97 Definitely didn't have that functionality.

2) However, there is one catch when using ADO. Client-side cursor must be used if the recordset is to be bound to a listbox, a combobox or a form.

3) When using recordset property, it is not necessary to requery as it would be if the recordsource was changed.

4) Normally, I would expect that a search form would be unbound. In your case, we're using a listbox, so that would be bound to a recordsource *OR* assigned a recordset (e.g. not both as that's bad mojo). We would then retrieve the selection from the listbox to get the primary key and move the main form to the correct row. Thus, the listbox's bound column should be the main form's primary key so you can pass it back to the main form.

Did that help?

 

[JezLisle]

No the only code I have for this Search Form is the Sub Form_Open()

I have taken the other Subs off to make sure I get this bit working first. The other subs are just for the ComboBox, TextBox and DoubleClick on the ListBox.

The RowSource Type in the Properties of the ListBox is Table/Query with no Row Source.

 

[DCrake]

Ok then give us a clue what do the sub do?

 

[Banana]

Aha, I see now. The thing is you are constructing a SQL, but you aren't assigning it to the listbox, which is why it is empty.

You would take the sQRY value and put it in listbox's rowsource property. You then no longer need the code.


PS Strike the previous statement; I was looking at wrong code. Still, the suggestion applies; you just need to put the SQL in the rowsource property and you won't need the code.

PPS IF this still doesn't work, one more thing I would look at would be changing the field "Name" to something else that's not a reserved word. Even though you're bracketing the field's name, there's no guarantee that Access won't get confused.

 

[JezLisle]

Banana from your point3 I have removed requery.

Running again the code below, I get another error 3024
Could not find file 'F:\My Documents\jez.mdb

What is this?

Private Sub Form_Load()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
        cnn.Open "Provider=sqloledb;Data Source=C****1;Initial Catalog=*******;Integrated Security=SSPI;"
'*************************************************************************************
    DoCmd.SetWarnings False
    Call UnLockAll
'****************************************************************************
        sQRY = _
            "SELECT [Name], DateOfBirth, PatientRef  " & _
            "FROM jez.IC_ReferralRecord " & _
            "WHERE InputBy <> 'DONOTDELETE' " & _
        "ORDER BY DateOfBirth, [Name] DESC "
    rs.CursorLocation = adUseServer
    rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
    rs.Close
'****************************************************************************
    Me.RecordSource = sQRY
    Me.lstSearch.RowSource = sQRY
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
End Sub

your point4 can you explain as I dont quite understand it?

 

[JezLisle]

Ok then give us a clue what do the sub do?

These below are the other sub() on the form.

Private Sub txtInputSearch_Change()
Dim cnn As ADODB.Connection
Dim sQRY As String
'*************************************************************************************
    Set cnn = New ADODB.Connection
        cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"
'*************************************************************************************
    Call UnLockAll
    If Not IsNull(cboSearchOn) Then
        If Not IsNull(Me.txtInputSearch.Text) Then
            sQRY = _
                "SELECT PatientName, DateOfBirth, PatientRef " & _
                "FROM jez.IC_ReferralRecord " & _
                "WHERE jez.IC_ReferralRecord." + Me.cboSearchOn + " LIKE '*" & Me.txtInputSearch.Text & "*' " & _
                "AND InputBy <> 'DONOTDELETE' " & _
                "ORDER BY DateOfBirth, [Name] DESC "
                
        Me.lstSearch.RowSource = sQRY
        End If
    Else
        Me.cboSearchOn.SetFocus
        Me.cboSearchOn.Dropdown
    End If
End Sub

Private Sub lstSearch_DblClick(Cancel As Integer)
    Call UnLockAll
    If IsNull(Me.lstSearch) Then
        MsgBox "Select from the list", vbExclamation
        Exit Sub
    End If
    Form_frmInputData.Initialise Nz(Me.lstSearch, "")
    Form_frmSearchRecords.Visible = False
End Sub

Private Sub cboSearchOn_Change()
    Call UnLockAll
    Me.txtInputSearch.SetFocus
    Call txtInputSearch_Change
End Sub

 

[Banana]

Let's take a step back and make thing simple.

1) Comment out the code.
2) In design view, select the listbox in question and go to its property RowSource under Data tab on Properties windows (alt-Enter)
3) Put this in the Rowsource:

SELECT [Name], DateOfBirth, PatientRef FROM jez.IC_ReferralRecord WHERE InputBy <> 'DONOTDELETE' ORDER BY DateOfBirth, [Name] DESC;

4) Save the form and open it normally. Does the listbox now load correctly?


In regards to my point 4, it is possible i may not be completely understanding your needs, but from what I read, it sounds like you have a main form where you do the usual work, but when you need to search for a record, you open another form with that listbox, and select a record, which the search form closes and the main form moves to that selected record. If that is your intention, then my point is that you should not need to set the search form's recordsource; only listbox needs its rowsource filled with the SQL.

Did that help?

 

[JezLisle]

Aha, I see now. The thing is you are constructing a SQL, but you aren't assigning it to the listbox, which is why it is empty.

You would take the sQRY value and put it in listbox's rowsource property. You then no longer need the code.

But as I'm not linked to the tables how can I do this?

 

[JezLisle]

Let's take a step back and make thing simple.

1) Comment out the code.
2) In design view, select the listbox in question and go to its property RowSource under Data tab on Properties windows (alt-Enter)
3) Put this in the Rowsource:

SELECT [Name], DateOfBirth, PatientRef FROM jez.IC_ReferralRecord WHERE InputBy <> 'DONOTDELETE' ORDER BY DateOfBirth, [Name] DESC;

4) Save the form and open it normally. Does the listbox now load correctly?


In regards to my point 4, it is possible i may not be completely understanding your needs, but from what I read, it sounds like you have a main form where you do the usual work, but when you need to search for a record, you open another form with that listbox, and select a record, which the search form closes and the main form moves to that selected record. If that is your intention, then my point is that you should not need to set the search form's recordsource; only listbox needs its rowsource filled with the SQL.

Did that help?

I have tried points1,2&3 the problem is that I am trying to connect to the tables that are not linked to the Access FE, when opening the form it tells it cant find file 'F:\My Documents\jez.mdb

Point4 Yes, I have a main form that will do all the inputting. This search form is designed to be able to click CmdButton on the InputForm and then open SearchForm. The ListBox I want to show, the Name, DOB & PatientRef. The User can then DoubleClick on the record they want and then it then shows in the InputForm. How can I only set the rowsource to the ListBox and not the Form?

 

[Banana]

Oooo, now I understand why you were doing it the hard way; I must have missed that point.

Any particular reason why you aren't linking tables?

Even if we didn't want to link tables, you still can write queries referencing tables not linked by filling in the ODBC connect string property of the query in the query builder. The string should be same one as you used in code (formatted as single unbroken string without quotes of course).