unbound search field

S

slimjen1

Registered User.
Local time
Today, 00:36
Joined
Jun 13, 2006
Messages
562
All, I am working in a 2003 database. I have a form with a subform. There is an unbound field to select a tracking number that pulls up all the cooresponding records in the subform. I created an alternative search on the sales number to pull up the cooresponding records in the subform if I didn't have a tracking number. It's not working and I've been trying for two hours to get this right. When I search google on "searching the field, I only get help when the subform is already populated with records and just need filtering.

Why does the combo box work for the tracking number and not the sales number? The tracking number is used as a link child for the subform but it's also an unbound field to search. I cant figure this out and I know its really simple.
Please help.
Thanks
 
Last edited:
duly noted on the crosspost.

To answer your question; I am using the same recordsource as the first search. They are both unbound comboboxes of course with different fields. I don't understand why and can't have both on the same form to have the option to search by either field and get the results to display in the cooresponding subform. I must be missing something.
Thanks
 
Perhaps you need to post the codes you're using for both the working and the non-working Comboboxes.

What are the Datatypes for the sales number and the tracking numbers? If one is defined as Text and the other defined as Number, the syntax for the code to do the searches would not be the same.

Also, how are you invoking the searches? Are you using the AfetrUpdate event of the Comboboxes or a Command Button(s)?

Linq ;0)>
 
both datatypes are text because of alphanumeric data. The tracking num combo box is based on a query. The form and subform are linked by the tracking number fields. This search/filter works but I also would like to search by an alternate field in the same query. When I set this up; I can select a record from the drop down but the cooresponding records in the subform are not displayed like the tracking combo box.
 
John Big Booty said:
I have no idea what you have done :confused:

Try the attached. However it only filters the subform based on one match.
Click to expand...

Ok. I did this but still can't quite get it to work. I ve attached a sample copy of the database. I tried to incorporate your code into the copy form: lessthantruckload1.
The main search is the invoice number. I need to keep this but have an alternative when users open database and want to search by pronum instead of invoice num because they don't know the invoice num.
I appreciate any help you can give me.
Thanks so much
 
Last edited:
Hi All. I am still stuck on this. Trying to do an alternate search and still have the fields populated both the subform and the items on the main form. I can search by invoicenum but still trying to get the other search as an alternative to the invoicenum. I've attached a sampledb
Appreciate any help.
 

Attachments

Last edited:
We're talking about two different record sources here, one for the main form and one for the subform. Since the PRO number is in the subform, and the subform can only display records that are related to the current record in the main form, then your current approach won't work. What you'll need to do is search the underlying table for the PRO number that was entered in the search box and, if found, retrieve the Invoice number (foreign key value) for that record. Then, once you have that Invoice number you bookmark your main form to that same invoice number. This is done in the After Update event (not the Change event) of the PRO search text box. John's example was for a dynamic, search as you type scenario, which is why he used the Change event (which fires at every key stroke). That's not what you want here. You want to search after the user has entered the entire PRO number. The code for this is;

Code: 
Private Sub Pronum_AfterUpdate()

Dim strInvoice As String

'Look for the Invoice number in the Ship Info table based on the PRO number entered in the search box.
strInvoice = Nz(DLookup("txtINVOICENUM", "tblLTLShipInfo_test", "txtPRONUM=""" & Me.Pronum & """"), "")

If strInvoice <> "" Then 'PRO number was found so reset the bookmark based on the returned Invoice number.
    With Me.RecordsetClone
        .FindFirst "txtINVOICENUM=""" & strInvoice & """"
        Me.Bookmark = .Bookmark
    End With
Else 'PRO number was not found so display a message box
    MsgBox "The PRO number you entered was not found."
End If

'Clear the search box.
Me.Pronum = ""

End Sub

I have reattached your db. If you enter a valid PRO number in the search box, the main form (and consequently the sub form) will bookmark to whichever record has that PRO number in the subform. I am assuming here that PRO numbers are not repeated (i.e. a given PRO number will only be related to one invoice). If that's not the case then you would need an intermediary step so the user could select which invoice they want to view.
 

Attachments

Thanks so much. It's working the way i want it but after a few trys and clearing the boxes I get this error: "update or cancelupdate without addnew or edit" and when I debug it points back to the "Me.Bookmark = .Bookmark" line.
What can I do to resolve this issue. I would like to put a clear all button on the form if I need it.

Thanks
 
This is happening in the sample db, or in your actual db after you added the code? It sounds like you might have some code elsewhere in the form that is creating an error when the attempt is made to set the bookmark. You can try commenting out your other procedures one at a time and testing to see if/when the error occurs. If you can create another sample db in which the error can be duplicated I can take a look. If so you need to let me know exactly what conditions cause the error. In the one I modified I tested it several times and never had an error.
 
Hi. It is also happening in the sampledb. I have the main search on the "invoicenum in the header. I think thats causing this. I wanted an alternative search i.e. pronum if the user didnt know the invoicenum.
The code for the invoicenum is a combo box called combo41(havent named this yet) It's similar to the code for the pronum. Could this be the problem?
Thanks
 
The only way I could get the error to raise is if I change a value in another field on the main form after performing the search, then try to execute another search while still on the same record in the main form (which hasn't been saved yet). You can trap for this by checking the form's Dirty property before resetting the bookmark. Setting Dirty = False will save any changes;

Code: 
Private Sub Pronum_AfterUpdate()

Dim strInvoice As String

'Look for the Invoice number in the Ship Info table based on the PRO number entered in the search box.
strInvoice = Nz(DLookup("txtINVOICENUM", "tblLTLShipInfo_test", "txtPRONUM=""" & Me.Pronum & """"), "")

If strInvoice <> "" Then 'PRO number was found so reset the bookmark based on the returned Invoice number.
    If Me.Dirty Then Me.Dirty = False [COLOR="Red"]'<<< New Line[/COLOR]
    With Me.RecordsetClone
        .FindFirst "txtINVOICENUM=""" & strInvoice & """"
        Me.Bookmark = .Bookmark
    End With
Else 'PRO number was not found so display a message box
    MsgBox "The PRO number you entered was not found."
End If

'Clear the search box.
Me.Pronum = ""

End Sub
 
ok. Now I get another error: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entires and try again."

I have two search boxes on the form. I also added a refresh button to clear the fields between searchs. These errors seem to be happening when I go between search fields. I was just testing. Im not sure this would happen often. I tried "the if dirty" but the error when debug points back to to me.dirty= false in the Combo41 search field. What can be the problem. I get so close:(
I have attached a copy of the database sample.

Thanks
 

Attachments

I think the problem is the search field for the pronum thinks its trying to write to the table when in fact it is only trying to search and retrieve the pronum associated with the table. and it;s raising the error. When I debug it highlights the me.dirty=false. How can I correct this and stop the error?
Thank you
 
You must log in or register to reply here.

Similar threads

M
Open Unbound form and filter subform
Replies
7
Views
309
Gasman
Gasman
M
working with unbound field in a form
Replies
8
Views
328
CJ_London
C
D
Solved Form jumping around in records when i field is typed
2
Replies
31
Views
645
mike60smart
mike60smart
Q
wrong search results
Replies
7
Views
283
Pat Hartman
P
O
Solved Unbound Form, Two Subforms: Checkbox Selection for Product-Hotel Association
Replies
7
Views
583
Odeh Naber
O

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom