Search engine..

[HZM]

Hello ..
I've a Form that contains 4 subforms in datasheet view in which each Form is created from a table.
The 4 Subforms are:
1) Data : contains 'Serial' field in autonumber format.
2) Invoices : contains File no, Invoice no, Invoice amount & BL no fields.
3) Payments : contains File no field.
4) Others : contains File no field.

First:
I want to link the 'Serial' field in the Data Subform with the 'File no' field in the other 3 Subforms..

Second:
I want to create a search engine by using a combo box and a text box in which the combo box represents the search type that can be by 4 ways (File no, Invoice no, Invoice amount & BL no) and when selecting the (File no) as a search type it search in the Data subform in 'Serial' field by the file no that will be entered in the text box next to the combo box..
Same when i choose the (Invoice no) as a search type it search with the invoice no that will be entered in the text box in the 'Invoice no' field in the Invoices subform and show only the Data related to it in the other subforms..

Third:
Next to the search text box there's a 'New File' button, i want it to create new record in the 'Serial' field in the Data subform (jump to 'New' record) when this button is pressed..!!

Thanks in advance..

 

[mdnuts]

For question 1 - do you mean set relations as in table level?

For question 2:
On the after update for Search Terms you could do something like this for VBA. (untested)

Dim sSQL as String
Dim db as DAO.Database
Dim rs as DAO.Recordset

set db = currentDb

sSQL="Select * from <tablename> WHERE '" & <searchbyfieldname> & "'='" & <searchtextbox> & "'"
set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
if rs.EOF Then
  msgBox "no records found"
ELSE
  Do Until rs.EOF
 '  do something with the results - put into table, etc.
  rs.MoveNext
  Loop
END IF
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

that could differ somewhat if the various search fields are not all text.

For question 3 - this will probably answer what you are looking for
http://www.access-programmers.co.uk/forums/showthread.php?t=68660

 

[HZM]

Well, table level contains so many relations, but i just need to set relations between the 3 subforms (Invoices, Payments & Analysis) by using the 'File No.' field with the 'Serial' field in the subform (Data)..

Also these codes didn't work with me.. !! :/

 

[jdraw]

It might be easier to understand if you first showed readers your tables and relationships.
That is the backbone of your database. Forms and subforms are really an interface to the data in the tables.

See the example search here.

 

[HZM]

I really tried many times to upload a sample of my DB but that's hard cause all my DB samples are more than 2 MB - even in ZIP formatting..!!!
Also i can't insert a link to an uploaded file since my posts didn't exceed the 10 posts .... :/
So, what else can i do than explaining and showing ScreenShots .. xD

 

[jdraw]

You don't have a sample or test database to demonstrate the issue that is less than 2 MB zipped?
I think a screen capture of your tables and relationships might help readers.

 

[HZM]

That's so easy, here's a ScreenShot of the relationships..

 

[jdraw]

When you capture the relationship windows, please extend the boxes so readers can see all the fields involved. Also, a brief description of each table and the purpose of the database puts the picture into context.
Lcs Data and LCs analysis means nothing to readers.

You have Invoices and Payment -- no Customer/Supplier???
Here's a generic model re Customers and Invoices that may shed some light on the issue.