To search record

[puT3]

I want to create a search form for a library which contain 2 option group;1 is for searching according to keywords,exact match,first on field and phrase,another is type of searching which have different field table such as title,publication,subject,author,call number,ISBN, and any. and also a text field to enter searching.When the search button is click,the result will appear in another form line by line.Do i need to create a query?I dont know ho to create the coding,I get confuse.I am very new to this.

[NeoPa]

Another question I want to asked,is it possible to keep the old data after being update?
For example, someone lend a book at a certain e.q.23/09/08 and return it 25/09/08 then someone else lend the same book on 27/09/08...I want to keep the previous date as a history,maybe in table or report..how can i do this?

This depends entirely on the design of your database, and is too complicated for me to issue simple instructions.

You need to consider exactly what you want. Will you keep all the data in one table (in which case it is important that the PK does not preclude multiple entries). This would also mean that any references to the table need to handle multiple entries, and select only the current one if that is what is required.

An alternative would be to have a separate history table (generally less recommended, but possibly easier to apply AFTER you already have a working database), where records could be copied when they are no longer current. Care needs to be taken with either approach and, while I can say the first is probably the better way when done properly, I will not be able to take you through this as it depends very much on precise information.

[puT3]

This depends entirely on the design of your database, and is too complicated for me to issue simple instructions.

You need to consider exactly what you want. Will you keep all the data in one table (in which case it is important that the PK does not preclude multiple entries). This would also mean that any references to the table need to handle multiple entries, and select only the current one if that is what is required.

An alternative would be to have a separate history table (generally less recommended, but possibly easier to apply AFTER you already have a working database), where records could be copied when they are no longer current. Care needs to be taken with either approach and, while I can say the first is probably the better way when done properly, I will not be able to take you through this as it depends very much on precise information.

Oh i see....there is nothing you can do for me,is it?...I already set up a new table [History] which I thought will be simple at first but I think I would like to keep in one table which is [Printing Media Library] if possible...I make a form [Update Date] which has a button [cmdSaveUpdate], its function to save and update the data in [Printing Media Library] table,recordsource is [QueryDef]...If you can help...

[puT3]

Sorry I have to ask this question here, I try to make a security of my database using Tools-Security...how to make it run without the login form appear
................Its ok i found the solution

[NeoPa]

Oh i see....there is nothing you can do for me,is it?...I already set up a new table [History] which I thought will be simple at first but I think I would like to keep in one table which is [Printing Media Library] if possible...I make a form [Update Date] which has a button [cmdSaveUpdate], its function to save and update the data in [Printing Media Library] table,recordsource is [QueryDef]...If you can help...

I can only really help with some general points here. To be honest, this whole area can get quite fiddly, and this sort of thing can only be done with good reliable levels of communication. A forum doesn't make this easy, and your past history doesn't lead me to believe you are up to the task.

So, some general pointers that may help, before I sign off. When storing expired data in the same table as live data, there are some issues you should be aware of.

1. There will need to be (at least) one extra field in the record to handle indicating which records are current and which are expired.
2. You may have many sections of your project which already access this table, but which most probably need changing to manage the expired records existing where previously there were just live ones.
3. You will probably have some sort of Unique index of fields which define your items. This index (these indeces) will need to be changed to incorporate any new fields which indicate the status of the record.

Point #2 may indeed be wide-ranging. Be very careful of this.

As an illustration, say we have a simple table ([tblProduct]) which contains all live product info.
Table Name=[tblProduct]

Field           Type        IndexInfo
ProductID       AutoNumber  PK
ProductCode     String      Index
ProdDesc        String      FK
Price           Number

When we add the field [ExpiryDate], notice how it must be added into the Index (it now becomes a Compound Index). If this is not done then adding a current product (No value in [ExpiryDate]), after expiring the previous one, would fail.
Table Name=[tblProduct]

Field           Type        IndexInfo
ProductID       AutoNumber  PK
ProductCode     String      CompoundIndex
ExpiryDate      Date        CompoundIndex
ProdDesc        String      FK
Price           Number

I hope this helps. I suspect it's as far as I can go on this problem.

[NeoPa]

Your latest question is not related to this thread, so I will move it across into it's own thread (Login Form) for you.

It has a link back to this one for anyone interested in reading back.

[puT3]

Hi again...In the previous post I have asked about how to transfer every data from [Printing Media Library] form to report so that it can be printed. I used this coding as you give it to me.However,the problem is the record do not come out in the report...

Private Sub cmdPrintingReport_Click()
 
    Dim strReportName As String
    Dim strCriteria As String
 
    strReportName = "Printing Search Result"
    strCriteria = "(([ISBN/ISSN] = '" & Me![ISBN/ISSN] & "')"
    strCriteria = strCriteria & " AND ([Topical Headings] = '" & Me![Topical Headings] & "')"
    strCriteria = strCriteria & " AND ([Publication Name] = '" & Me![Publication Name] & "')"
    strCriteria = strCriteria & " AND ([Title] = '" & Me![Title] & "')"
    strCriteria = strCriteria & " AND ([Author Name] = '" & Me![Author Name] & "')"
    strCriteria = strCriteria & " AND ([CallNumber] = '" & Me![CallNumber] & "'))"
 
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
    DoCmd.Close acForm, "Printing Media Library"
 
 
End Sub
 

I try it on another form it works ok except that in the report it only display on records only when it have many records

[NeoPa]

This doesn't look like code I would post. Can you point me to where I posted it?

[NeoPa]

I try it on another form it works ok except that in the report it only display on records only when it have many records

If you could explain this too please. It doesn't make sense to me :S

[puT3]

This doesn't look like code I would post. Can you point me to where I posted it?

Post #144 but its ok, i tried it again and it work,maybe somewhere in the code i type in was wrong...The only problem is when the record it appear on report,it only shows 1 record only in one page when they should be more of one records...For example, when user search for title of book named "design" (in the table have many books titled design), it appear correctly in result form but when I want to print it and i click a button (the button contains the aboce coding),it only shows of one record, the user need to go back to and go over the same process

[NeoPa]

That actually makes perfect sense.

The solution was to open the report with the currently selected record. Not with the same selection of records that are searched for in the form.

If you look back to post #144 you will also see a recommendation of how to use the filter that is already set for the form. If this is what you want than it is probably an even easier solution.

[puT3]

Oh i see...thanks...

[NeoPa]

No worries :)

Let us know how this works for you.

[NeoPa]

It works well..Another question is ...

If this thread is complete now (as it appears to be), I will split these new questions into (at least one) new threads (Delete Records from a Form).

I can include links across so that you can easily find them again.