copied form

[skate]

now this may sound silly, but I had a form that could search for items based on values entered by the user and displayed in a listbox. It works perfectly, so I thought I would do a "save as" and make a second record searching form very similiar except for the minor changes in labeling and table source and field names, etc. but it doesn't work! This completely puzzles me because it should function exactly as the other form, but when I try to do a search (enter text) my listbox goes blank. I've checked that all the labeling/names have been changed. Why???

 

[ajdesalvo]

Have you missed any form references?

For example, somewhere it says Forms![Form1] where it should now say Forms![Form2]

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[skate]

no, that's the thing, I don't get it. I'll have to stare at it again.

 

[beetee]

If you're spending *too* much time staring at it, you may want to try doing the 'save as' step again; but not make any changes in control names.

Then, keep retrying the form as you make small changes.

 

[skate]

ok, i think I've figured it out, but don't understand enough about VB to know why.

here is my code from the form not working:

Private Sub txtSearch_Change()
Dim txtSearchString As Variant
Dim strSQL As String
txtSearchString = Me![txtSearch].Text
strSQL = "SELECT [Supplier].[Supplier Index], [Supplier].[Company], [Supplier].[Location], [Supplier].[Country], [Supplier].[Type of Work], [Supplier].[Description], [Supplier].[Item General] FROM Supplier"
strSQL = strSQL & "WHERE ((Files.[Type of Work]) Like '" & txtSearchString & "*') "
strSQL = strSQL & "ORDER BY Supplier.Company"
Me!lstResults.RowSource = strSQL
Me!lstResults.Requery
Me!txtSearch.SetFocus

The only difference from that form and the working one is that I replaced this piece of code, where there is a "INNER JOIN"??? and i noticed that is why the above doesn't work. ...help...

SELECT Files.[File Index], Files.[Hot List], Customer.[Customer ID], Files.Project, Files.Eng, Files.Proposal, Files.[Mill Company], Files.[Mill Location], Files.[Detail Desc], Files.[Cust PO No], Files.Date FROM Customer INNER JOIN Files ON Customer.[Customer Index] = Files.[Customer Index]

 

[ploeryan]

Hello

It appears you are only referencing one table so if you replace where you have (this references the table File):

strSQL = strSQL & "WHERE ((Files.[Type of Work]) Like '" & txtSearchString & "*') "

with (this references the table Supplier):

strSQL = strSQL & "WHERE (([Supplier].[Type of Work]) Like '" & txtSearchString & "*') "

You do not have to wwoirry about the inner join as there is only one table being referenced so there is no link to another table.

I hope this works for you....[Afro2]

 

[skate]

Oh, I didn't even notice that error. Thanks. Unfortunately, the form still doesn't work. I can't figure it out.