Search function locks data for one record after open a form. 1

[ychousa]

Hi. I have a search form with 4 unbound text boxes, a command button for search, and a listbox to show search result. When double click the result, a main form(frmMain)opens.

The problem is the record on the Main form is locked and wouldn't move to a next record.

Here's my code for search button:
Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String, strSQL4 As String, strSQL5 As String, strOrder As String, strWhere As String, SQLStmt As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
'tblMain.SID, tblMain.SName, tblDetail.SYear, tblDetail.SStartMonth
strSQL1 = "SELECT tblMain.SID, "


strSQL2 = "tblMain.SName, "


strSQL3 = "tblDetail.SYear, "

strSQL4 = "tblDetail.SStartMonth, "

strSQL5 = "tblDetail.DetID " & _
"FROM tblMain INNER JOIN tblDetail ON tblMain.SID = tblDetail.SID "

strWhere = "WHERE"

strOrder = "ORDER BY tblMain.SID;"

'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtSID) Then '<--If the textbox txtSID contains no data THEN do nothing
strWhere = strWhere & &quot; (tblMain.SID) Like '*&quot; & Me.txtSID & &quot;*' AND&quot; '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtSName) Then
strWhere = strWhere & &quot; (tblMain.SName) Like '*&quot; & Me.txtSName & &quot;*' AND&quot;
End If

If Not IsNull(Me.txtSYear) Then
strWhere = strWhere & &quot; (tblDetail.SYear) Like '*&quot; & Me.txtSYear & &quot;*' AND&quot;
End If

If Not IsNull(Me.txtSStartMonth) Then
strWhere = strWhere & &quot; (tblDetail.SStartMonth) Like '*&quot; & Me.txtSStartMonth & &quot;*' AND&quot;
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

SQLStmt = strSQL1 & &quot; &quot; & strSQL2 & &quot; &quot; & strSQL3 & &quot; &quot; & strSQL4 & &quot; &quot; & strSQL5 & &quot;&quot; & strWhere & &quot; &quot; & strOrder


Me.SearchList.RowSource = strSQL1 & &quot; &quot; & strSQL2 & &quot; &quot; & strSQL3 & &quot; &quot; & strSQL4 & &quot; &quot; & strSQL5 & &quot;&quot; & strWhere & &quot; &quot; & strOrder


End Sub

And Here's the code for listbox:
Private Sub SearchList_DblClick(Cancel As Integer)

Dim Criteria As String
Dim CurDB As Database

DoCmd.ShowAllRecords

DoCmd.OpenForm &quot;frmMain&quot;, , , &quot;[SID] = '&quot; & Me![SearchList] & &quot;'&quot;, , acDialog

End Sub

The Main form is bound to tblMain which only has SID(primary key) and SName. The Main form has a subform [tblDetail Subform] which has a variety of subforms in it, which has DetID as a primary key.

How could I make the opened frmMain functions correctly as it is opened directly?

Thanks in advance.

John

 

[RoyVidar]

Hi!

From your code, I gather that by doubleclicking the result, you are doubleclicking one list item of your result (listbox), and what the code performs is to open a new form where the filter is set to that list items SID - ie - it's filtered to show that, and only that record. Normally you should be able to toggle between that record and new record.

To get all records, the user could click the &quot;Remove filter&quot; button (if the form wasn't opened in dialog mode) or you could add a toggle filter button with 'me.filteron = not me.filteron' or something in the frmMain) - now that will toggle between the complete recordset of the table and the one you're using in the filter. I don't think thats what you want.

I think you want to be able to browse thruough all the records in the result list, If that's correct, you might find some tips here:

There are a several alternatives available, for instance
* having the frmMain show all the records currently shown in the listbox result

For simplicity I'll assume you are showing the excact same fields in your frmMain as you're showing in your listbox.

Then in stead of using the current selected record from the listbox as a filter criteria, send the whole row source of the list as open arguments:

DoCmd.OpenForm &quot;frmMain&quot;, , , , , acDialog, Me.SearchList.RowSource

Then, in the frmMain open event, assign the forms recordsource the value of OpenArgs

Private Sub Form_Open(Cancel as Integer)
if len(me.openargs & &quot;&quot>0 then
me.recordsource=me.openargs
else
msgbox &quot;No data selected&quot;
cancel=true
endif
End Sub

If this is the direction you want, but need &quot;lots of other fields&quot; in frmMain, you might then for instance consider building the forms complete recordsource for both the listbox and the frmMain and pass it, or perhaps pass only the filter criteria and build the frmMain recordsource (sql-string) in frmMain on open/on load.

You could then also have a toggle button to toggle between the openargs recordsource and the table recordsource thru a button (like with the mentioned filter toggeling):

Sub cmbToggleButton_OnClic()
if me.recorsource<>&quot;tblMain&quot; then
me.recorsource=&quot;tblMain&quot;
else
me.recordsource=me.openargs
endif
end sub

Please report back if you're persuing other path to solutions.

HTH Roy-Vidar

 

[ychousa]

Hi.Roy. Thanks for your comments.

I tried your suggestion and it works fine with toggling. But frmMain opens with the first record in listbox's recordsource whatever I select in the listbox.

How could I open frmMain with a selected record from listbox record source? If it's too complicated, I could just go for search by SID and enable toggling-I tried this but got an error in the line&quot;me.recordsource=me.openargs&quot;in toggle button onclick.

Another problem that I have been stumped with this search function more than a week is to find a subform record.

Could you give me an advice to resolve the problem with this case?

As I explained, frmMain is bound to tblMain which only has SID and SName. frmMain has a subform [tblDetail Subform], bound to tblDetail with primary key as DetID, and tblDetail is connected by SID with tblMain. [tblDetail Subform] has a variety of subforms in it, all identified with DetID.

Basically, one SID has many years(many DetID) in tblDetail . Because [tblDetail Subform] is identified by DetID, I called the value in strSQL5 in my first post.

But with this search function, I only get the first record on [tblDetail Subform]under an SID.

I spent a week to figure out this, searching this site and entire internet, but couldn't find exact answer.

If you can help with this, I really appreciate it.

Thank you.

John

 

[RoyVidar]

Hi!

Short answer on:
“But frmMain opens with the first record in listbox's recordsource whatever I select in the listbox.”

I think the reason you're getting the first SID, is the “showallrecord”-thingie (on my form, showallrecords moved current record from N to 1 when it was invoked – so that's probalby the reason). So remove the lines

dim curdb as database
docmd.showallrecords

from you dblclick-routine – that should (hopefully) give you the correct record

Have been away for a while, sorry, trying to work on the rest at the moment. Finally found out you want to open the frmMain with the selected record and be able to browse the others.

Roy-Vidar

 

[ychousa]

Hi, Roy.

I commented out docmd.showallrecords, but still the same result..

This is my guess, but if &quot;DoCmd.OpenForm &quot;frmMain&quot;, , , , , acDialog, Me.SearchList.RowSource&quot; in the listbox sends the whole set of search list row source, how frmMain gets a selected record?

I'm not sure if dbclicking an item in the listbox sends only the selected record or the whole set of row source.

Is there a way to check it such as debug.Print XXXX?

Thanks.

John

 

[RoyVidar]

One are sometimes a bit forgetfull, sorry, should have stated that this would involve going back to (part of) the original code in the dblClick routine.

The following is based on the original solution, you will then also have to remove the suggested lines from frmMain's on open event.

(As you suggest, you might test it with Debug.Print. Try it both with and without showallrecords)

BTW - is SID numeric or text? If its numeric, replase the openform line with
DoCmd.OpenForm &quot;frmMain&quot;, , , &quot;[SID] = &quot; & Me![SearchList], , acDialog

Private Sub SearchList_DblClick(Cancel As Integer)
Debug.Print Me![SearchList]
DoCmd.OpenForm &quot;frmMain&quot;, , , &quot;[SID] = '&quot; & Me![SearchList] & &quot;'&quot;, , acDialog
End Sub

Try this first, If it works, then you afterwards might consider the whats below. If it doesn't work, let's work on it. Then I'll assume some other code on the form (i e in the forms on current event) is making trouble.

If you wan't to FIND the matching record in frmMain, and still be able to browse thru all records, try the following.

Remove, even more from the dblclick routine, I think we'll setle for just opening the form in acdialog mode, so the only remaining line in the routine should read:

DoCmd.OpenForm &quot;frmMain&quot;, , , , , acDialog ' I think this is the correct number of commas

The recordsource for frmMain should continue being tblMain. In the forms on load event, you could use the following code:

private sub form_load
dim rs as dao.recordset
on error goto
set rs=me.recordsetclone
rs.findfirst “[SID] = “ & forms!frmSearchFormName!SearchList
if not rs.eof then me.bookmark=rs.bookmark
set rs=nothing
end sub

NOTE:
* this assumes SID is a numeric field, if not, replace the findfirst line with:
rs.findfirst “[SID] = '“ & forms!frmFormName!SearchList & “'”
* frmSearchFormName = name of the search form (where you have the dblclick-routine and listbox)

MISC
* If your cmdSearch_Click is unchanged, and you posted the whole sub, you could also there remove some lines there. The following lines don't seem to have any use:

Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

* Don't know why the toggle thingie didn't work, but you shouldn't need it now. If you're using the filter solution (previous post) the toggle filteron thingie as mentioned in my first post, should work in this form also.

SUBFORM
Are you saying that when you open frmMain with the selected record from tblMain, you only get one record i subform detail?

Check the Link “Child Fields”/”Link Master Fields” properties for the subform. They should both be SID.

If this isn't what's needed, report back, and we'll try again.

Roy-Vidar

 

[ychousa]

Hi, Roy. It works beautiful! Thanks.

Here's another bugging to you.

The subform problem I have is that the searchlist box only sends SID without any parameter. I confirmed it with Debug.Print statement.

So even though frmMain successfully opens with an SID, the corresponding subform [tblDetail Subform]inside frmMain shows the first record with the SID whatever record I choose in SearchList in frmSearch.

For example, an SID of &quot;S606&quot; has many years. Each year has its own DetID, so if I choose(in the SearchList box) &quot;S606, 2003, 37(DetailID)&quot;, the frmMain should show S606 and the [tblDetail Subform] inside it should show year 2003 whose DetID is 37.

The current situation is that even though I select above set of record, if SID S606 has year 2002 and DetID 36 as its first record, the opened form shows this set of record, not the one I selected.

If I explain my db structure once more, frmMain is bound to tblMain which only has SID and SName. frmMain has a subform [tblDetail Subform], bound to tblDetail with primary key as DetID, and tblDetail is connected by SID with tblMain. [tblDetail Subform] has a variety of subforms in it, all identified with DetID.

I think this is natural because I only parse SID from SearchList box.

I think what I want to do is to send SID and DetID from the listbox and open corresponding records.

The link between the forms are set correct.

I spent a week to find this answer, but not successful.

What would you recommend?

Thanks much.

John

 

[RoyVidar]

Where I am, it's a bit over bedtime, so I'm gonna do a CTRL+ALT+DELETE on myself, with the Shutdown option in a minute

Thanx for the star!

Here's something that might be a start for populating your subform.

From your subform you can access all the items i the searclist. How:

To get the bound column (as you probably know)
Forms!frmYourSearchForm!SearchList

To access what's in the other columns:
Forms!frmYourSearchForm!SearchList.column(1)
will access the 2' column of the list (it's 0-based, the bound column is number 0) - here SName if I read correct.

The above code can be used in code for instance:

Dim strSName as String
strName = Forms!frmYourSearchForm!SearchList.column(1)

or as a control source of a textcontrol:
=Forms!frmYourSearchForm!SearchList.column(1)

Good night, Roy-Vidar

 

[RoyVidar]

Hi again!

Yes with the syntax &quot;me.searchlist&quot; you only pass the SID

Just wondering, do you really need the frmMain? Since you ask for my recommendation, and based on all the information you have given, I say – skip the main form, and open the subform (frmDetail) as your new mainform. Why:
* It'll be easier (less forms to operate and push/pull data to and from...)
* The only thing I see you use the main form to is displaying SID and SName and the subform. (other items might be displayed in the frmDetail Header section)

But you probably wan't to display the SName and SID, so those will have to be included int the frmDetail recordsource (SQL-string which includes the SID/SName).

I'll try to make an approach to match both scenarios (with and without frmMain) and further try to distinguish where there might be differences between the approaches. I'd say the approach you are mentioning, either pushing or pulling the whole or part of the searchlist recordsource could be a nice starting point.

If I've understood you correctly you:
1 do a selection in searchform providing a subset located in searchlist
2 select one of the records in searchlist, and want to populate frmMain with the parent record (SID, SName)
3 not based on the the frmMain but on the searchform, you want to populate the frmDetail

That implies
1 providing a subset of data to the frmMain based on SID – which now is functioning properly
2 providing a subset of the subset in searchlist for the frmDetail – which we're working on

There are several approaches, I like building a “base” sql string, and like you a separate “criteria” string (this makes it possible to toggle between all records and the “filtered” ones, as you've tried out)

If you like the approach of having the “toggle” opportunity, I'd declare two form module level sql-string for frmDetail. Just below the “Option Compare Database and Option Explicit” lines declare your string:

private mstrSql as string ' This will make the variable available all the time the form is open
private mstrWhere as string

In the frmDetail on open event, assign values to both sql-strings:

mstrSql=”select whatever fields from tblDetail (perhaps inner join tblMain if you're removing frmMain)”

For the strWhere-thingie, you'll need to perform the exact same thing you're doing to populate the searchlist, but now using the selected item in the searchlist (I'll do one example using tblDetail field from your post). This approach also provides possibility to use several more criteria from the searchlist than SID and DetID.

' checking if selected item has any value in specified column
If len(forms!frmSearchForm!SearchList.Column(2))>0 then
if len(mstrWhere)=0 then
mstrWhere=” Where”
else
mstrWhere=mstrWhere & “ AND “
end if
mstrWhere=mstrWhere & “' ” & forms!frmSearchForm!SearchList.Column(2) “'”
end if
' ... continue criteria building (with this approach, you don't need to remove last “And”)...

me.recordsource=mstrSql & mstrWhere

Toggling. I'm not able to toggle the rowsource of the frmDetail thru the frmMain. I think it should be possible, just that I don't know how (with subs, you have not only the recordsource, but also the linking...).

However, If you choose to skip the frmMain, opening the frmDetail from the searchform, toggling between chosen record and all should be possible thru:

' if the sql-string contains the word “Where”
if instr(ucase(me.Recordset),”WHERE”)>0 then
me.Recordset=mstrSql
else
me.Recordset=mstrSql & mstrWhere
end if

HTH Roy-Vidar

 

[ychousa]

Good Morning, Roy.

Wow. I feel like I'm back to my university classroom with a cool instructor. Thanks.

The reason I made tblMain is that I wanted users to stay on an SID when they browse years' records.

If I decided to use just tblDetail, the record goes one by one, jumping to different SIDs, so I thought users might feel inconvenient about that.

Well, now that I have learned a bit of tips thanks to great instructors like you , I have a vague idea which encourages me to challenge the above problem with just tblDetail.

This morning, I'm stuck with some other urgent tasks. I will try your suggestion and report it back to you ASAP.

Thanks.

John

 

[ychousa]

Hi, Roy. Hyuu.. Today was so hectic and I couldn't have a chance to work on it.

This project is due early next week, so I think I have to spend my whole time to develop some required reports from my boss.

I think I would stick to frmMain for a while because frmDetail has a list box that users can select years for an SID, and I think that might give some convenience for them.

I will try your suggestion after a whle though, 'cause it sounds good.

Thank you so much for your instructions. I really appreciate that.

Have a great day!

John