Full Version: No Response from a Find Procedure
UtterAccess Forums > Microsoft® Access > Access Forms
quest4
Good afternoon. I created a little form which works great on one txtbox, it displays all available choices from a table and thru entries made in the unbound txtbox it reduces the available choice of the table. I thought I would try it to replace the find in Access, so I modified the procedure and when I click one of the possible selections, it should bring up that record in the main form, this is being run from a cmdButton on the main form. I have this procedure in the txtbox's OnClick event:
Dim myQCA As String
If SysCmd(acSysCmdGetObjectState, acForm, "frmQualityControlAction") Then
myQCA = Me("QCANo").Value
Me.RecordsetClone.FindFirst "[QCANo]=" & myQCA
If Me.RecordsetClone.NoMatch Then
MsgBox "Couldn't Find Quality Control Action No." & myQCA
DoCmd.Close acForm, "QCALookUp"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
DoCmd.Close acForm, "QCALookUp"
End If
Odon get an error or the debugger, just nothing happens when I click any of the possible selection. Can anyone suggest what is missing? Thank you in advance to anyone rendering assistance. confused.gif
Jack Cowley
Create a combo box on the form using the Wizard. On the Wizards first screen select the 3rd item, "Find a record..." Finish the Wizard. Now selecting the item you want from the combo box will show you the selected record. I prefer combo boxes as you do not need to worry about the user spelling the entry incorrectly or inputting a possiblility that does not exist....
th,
Jack
quest4
Thank you again for the response Jack. I first thought about what you recommended, but I kept working with the LookUps I have and I kept thinking this is better because I can type in partials and get everything that is in the ball park. The form that I am trying to get working is femQCALookUp, which works off of the cmdButton Find on form frmQualityControlAction. The other LookUps have been disabled because they use ODBC linked tables and they only work on one txtbox and this is for a entire record. I have attached a stripped donwn version of the dbase with a couple of simple records in the tables. Thank you again for all od the assistance.
Jack Cowley
When you say you are tring to get the form frmQCALookup working what, specifically, is not working as it seems to work for me.... By that I mean that if I select 2 in the text box it shows record 2 in the subform. Also, do you need a subform? Why not just a form with a text box?
ack
quest4
Thank you agin, Jack. Normally there would be alot of 5 and 6 digit number in the table. So, in frmQCALookUp, as you type the numbers into the txtsearch txtbox the displayed list, in the subform, shortens, then when you see the item you want, then click on it and the new record is displayed in to form. It works have nicely for Customer No and Part No, but those are ODBC linked tables so I couldn't send them along. I believe that the subform is needed because when the form opens, sometimes there maybe thousands of records displayed. Am I making myself clear? This is a glorified find record form, in short. Thank you again for all of the assistance.
Jack Cowley
See if the attached demo does what you want. Hopefully it will answer your question....
ack
quest4
Thank you for the attachment. Funny thing, that was the form that got me started on building my LookUpswhich are a simpler and better version of that form. The ideas are the same but I never found a way to get it to load the select record into the open main form, kind of like what find does. Oh well, maybe I will go back to find and use it. Thank you again for the assistance, I do appreciate it.
quest4
Jack I got it getting almost working. I am using this code:
Dim myQCA As String
If SysCmd(acSysCmdGetObjectState, acForm, "frmQualityControlAction") Then
myQCA = Me("QCANo").Value
Forms("frmQualityControlAction").RecordsetClone.FindFirst "[QCANo]=" & myQCA
Forms("frmQualityControlAction").Bookmark = Forms("frmQualityControlAction").RecordsetClone.Bookmark
DoCmd.Close acForm, "QCALookUp"
End If
The one thing it is supposed to is close the form. frmQCALookUp and it is not. It isn't turning on the cmdButtons in the subform of the main form either but I think I can probably figure that one out. Is there a reason why the form is not closing? Thank you again.
Jack Cowley
I guess I am not sure what you are trying to do so let me ask you if the Find form is to allow the user to select a value from the list box and when they close that form (QCALookup) that the form "frmQualityControlAction" now shows that selected record? If that is what you are trying to do then put this code in with the code in the Close button:
CODE
[Forms]![frmQualityControlAction].Filter = "[QCANo] = " & [Forms]![frmQCAlookup]![fsubQCALookUp].Form.QCANo
[Forms]![frmQualityControlAction].FilterOn = True

hth,
Jack
quest4
Thank you again, Jack. Yes, almost. The form frmQualityControlAction is open, with a record, click find and the frmQCALookUp opens. Type in a couple of digits and then locate the desired QCA No and the you click on thatQCANo and the frmQCALookUp form closes and teh select QCANo appears in the form, ready to be worked on. The cmdClose cmdButton is just temporary, till the form is forming correctly, in theorythe form frmQCALookUp should close as part of the procedure. The code I put above, looks like it is not getting the close statement and something else is wrong, like it may only be getting one the the four part in QCANo 1. The cmdButtons, cmdNext and cmdPrevious can not and do not become visible the subforms OnCurrent event should have turned them on, which leads me to believe that I should somehow be using a loop to get all of the part for eash selected QCAno. Is this clear? Thanks again for everything.
Jack Cowley
Replacing the code in the On Click event of the QCANo control in the subform on your QCALookup form with this code should show the selected record in frmQualityControlAction and close the popup.
CODE
[Forms]![frmQualityControlAction].Filter = "[QCANo] = " & [Forms]![frmQCAlookup]![fsubQCALookUp].Form.QCANo
[Forms]![frmQualityControlAction].FilterOn = True
DoCmd.Close acForm, "frmQCALookup"

Is there more that you need to do?
hth,
Jack
quest4
Thank you, It is now closing, frmQCALookUp that is. There is one small problem. in the form, frmQualityControlAction, on the subform the cmdButton still aren't visible when they should be. Shouldn't the subform's OnCurrent event prevent that from occuring? Much the way it works when the form open and you enter a QCA No. I almost wonder if all of the Part No are really there, the property is visible yes and the subform's OnCurrent event turns them on and off. Any thoughts on this? Thanks again for everything I appreciate it.
Jack Cowley
Try this code in the On Current event of the subform on frmQualityControlAction:
CODE
If DCount("*", "tsubQualityControlAction", "[QCAno] = " & Me.QCANo) >= 2 Then
        Me.cmdNext.Visible = True
        Me.cmdPrevious.Visible = True
        Exit Sub
    Else              
        Me.cmdNext.Visible = False                
        Me.cmdPrevious.Visible = False  
End If

Hopefully this will get you close to what you want...
Jack
PS You can get rid of the DCount() control (NoOfItems) on the subform.
quest4
Thank you again, but I get a systax errorelse without and if. It does not like my second else in the OnOpen event of the subform. I have a procedre in there that keeps the next key from generating a new part no record, by disable the next cmdButton when there are no more records ahead of tehcurrent record. Works pretty good. Would like to keep it. So here is what I got:
If DCount("*", "tsubQualityControlAction", "[QCAno] = " & Me.QCANo) >= 2 Then
Me.cmdNext.Visible = True
Me.cmdPrevious.Visible = True
Exit Sub
Else
Me.cmdNext.Visible = False
Me.cmdPrevious.Visible = False
Else
Dim rst As Object
Dim lngRec
Set rst = Me.RecordsetClone
If Not rst.EOF Then
rst.MoveLast
lngRec = rst.RecordCount
End If
rst.Bookmark = Me.Bookmark
Me.cmdNext.Enabled = Not rst.AbsolutePosition + 1 = lngRec
Set rst = Nothing
End If
I am not sure how to else if myself around this one, any suggestions? Thank you again.
quest4
Oh the new OnCurrent procedure does work with the rest commented out.
Jack Cowley
Now I see what you are up to. See if this code will do the trick for you...
CODE
Dim recClone As DAO.Recordset
Set recClone = Me.RecordsetClone
If Me.NewRecord Then
Me.cmdNext.Enabled = False
Me.cmdBack.Enabled = False
Else
recClone.Bookmark = Me.Bookmark
recClone.MovePrevious
Me.cmdBack.Enabled = Not (recClone.BOF)
If recClone.BOF Then
Me.cmdBack.Visible = False
Else
Me.cmdBack.Visible = True
End If
recClone.MoveNext
recClone.MoveNext
Me.cmdNext.Enabled = Not (recClone.EOF)
If recClone.EOF Then
Me.cmdNext.Visible = False
Else
Me.cmdNext.Visible = True
End If
recClone.MovePrevious
End If
recClone.Close

I have not tried this in your db.
Jack
Jack Cowley
I forgot to tell you in the last post to change cmdBack to cmdPrevious and to check the DAO 3.6 library in References.
The code does work...
Jack
quest4
Thank you Jack, but I get a compile error, user defined type nor defined and the debugger hi-lites Dim Dim recClone As DAO.Recordset
No w that make no sense to meDoes it to you. Thanks.
Jack Cowley
Open the code editor where you see the Debug menu. Click on Tools > References and look for: Microsoft DAO 3.6 Object Library. It may be near the top or you may need to scroll down to the M's. When you find it check it. Now the code will work.
My apologies for not giving you the details in my previous post.
Jack
quest4
Thank you Jack. That works better than the one I was using. thank you for all of your time and assistnace. yayhandclap.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.