Modifying record from a search result

[DanEaton]

I have set up a search form that is based on a query. It prompts the user for a product to search and displays all of the catalogs that contain at least that product (as there can be many). There is a button on this form called cmdModifyCatalog. This button, when clicked, activates the visible property of another button called cmdEdit which is located beside every search result record (the form is continuous). I want this edit button to open up my Catalog Entry form (frmCatalogDataEntry) and go to the record that the button is beside. In other words, if someone wants to modify or delete a catalog they will go to this search form with the product in mind (or vendor, there is a seperate vendor search form) and select the product that they wish to search, then modify the record when they find it. Once they are in the data entry form, anything can be done for editing. So my big problem is simply getting the data entry form to open up to the record that the user selects. Any ideas?





Dan Eaton
deaton@caemachinery.com

 

[thornmastr]

Relatively easy to implement. The record set being used for your continuous form probably contains a foreign key to the catalog (?) table. You want to include that key on the continuous form. It can certainly be invisible to the user. It can be hidden behind one of your other variables, or placed in the header or footer of the form.

Assuming the name of that variable is hiddenfk. Supposing that FK points to the primary ky (PK) of the catalog table. Assume also the name of the form you want to show is frmCatalog

When the user clicks on the catalog button, insert the following in the on click event of the button.

Dim strsql as string
Strsql = “PK = “& me.hiddenfk.value
Docmd.openform “frmcatalog”,,,strsql

You have it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[DanEaton]

Thanks for your reply thornmastr, I implemented your code into my edit button's On Click Event.

Private Sub cmdTrashCatalog_Click()

Dim strsql As String
strsql = "Forms!frmCatalogDataEntry!txtCatID = " & Me.txtCatID.Value
DoCmd.OpenForm "frmCatalogDataEntry", , , strsql

End Sub

It brings me to the other form, BUT, it takes me to a version of frmCatalogDataEntry that contains no records. In fact, it takes me to a new record. The record indicator says
1 of 1 (filtered). I can't figure this out. If I just wrote the second part of your code: DoCmd.OpenForm "frmCatalogDataEntry", , , strsql
I would imagine that it would open the form normally, so I can't see how the first part (which I agree should work) would change anything...I copy and pasted this code from the onClick event, so if there's a problem, I hope you can see it. Dan Eaton
deaton@caemachinery.com

 

[thornmastr]

Hi Dan,

The first problem I see is you are searching the result of two forms in strsql which will not work. Think of strsql as nothing but the where clause of a SQL statement without the word “where”. So you want to compare a key on your table record with a value on your form (the forward key) so that in your strsql= statement something like the following general construct

Strsql = “<table column name> = “ & me.txtcatid.value

The other thing I see is that you are dealing with a text key which, like any string, must be quoted so that would be

Strsql = “<table column name> = ‘“ & me.txtcatid.value & “’”

Notice the encapsulated quote for the string. the first example I gave you was for a numeric value.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[DanEaton]

Thanks, I think the problem was the first one that you stated. My logic was, open this data entry form to the record where the CatalogID is the same as the CatalogID of the record in the Search form (foreign key record source). But I guess what you're saying is that I can't do a where statement between two forms. I have just implemented the modified code based on the table's primary key. Everything works perfectly.

Thanks a lot, I feel like Tek-Tips has been vital to the progress of my first database.
Dan Eaton
deaton@caemachinery.com