Retrieving fields problems

[dwayne dibley]

Hi,

I am currently trying to develop a database based on a library system(as part of a college assignment).

The problem, I am having at the moment is regarding the customer returning a book. I would like to create a form which allows the user to input the book's ASC number into a text box, which will then retieve the loans details.

I currently have a loan table with fields Loan ID(auto-number) and then loaned date, returned date, member id(FK), status and ASC(FK).

I think this would need to be done via unbound text control, but im not sure how to attach this to the other fields.

No doubt this will be quite easy to resolve, but I'm fairly new to access.

Any help will be greatly Appreciated

 

[statsman]

The following advice is making certain assumptions. For example, your library has 4 copies of Tom Sawyer. Each copy has a unique ASC. If the copies share the same ASC, you will have to go into a much more complicated procedure to find the right book to enter as returned.

You will need to do the following:
1 Create a search form. Use Design view not the wizard. Place 1 unbound textbox on the form. Name the form BookReturnSearchForm

2. Create a query using all of the fields in your table. Name it BookReturnSearchQuery. In the criteria of the field ASC enter [Forms]![BookReturnSearchForm]![Text1] Where Text1 is the number of the text box on the BookReturnSearchForm.
In the criteria of the field ReturnedDate enter =0 (zero)

3. Create a Book Return form using BookReturnSearchQuery as the source (you may use the wizard to create this form). Name it BOOKRETURNFORM. Enter all the fields you want on the form which concern the book's return. On the bottom of the form enter 2 command buttons. The first closes the form, the second is labelled "RETURNED" (no quotes).

4. Go back to BookReturnSearchForm. Enter 2 command buttons at the bottom of the form. The first closes the form with no action taken. The second should be labelled SEARCH. Set it to open the form BOOKRETURNFORM.

When you enter an ASC in the search form and then click on search, the return form should open with the book you want on the screen. We want a loan with no return date or we will get a listing of all of the loans of that book that are in the database. This way we only get a listing of the outstanding loans for that book. I am using a blank return date field as an indicator that the book is out on loan.

5. With BOOKRETURNFORM in design view, right click the command button Returned. Select Build Code. In the top right corner of the screen there is a combo box. Click the down arrow and select On Click. In the blank area between the existing text, but just above "End Sub" enter: ([ReturnedDate] = Now()). This will enter todays date into the return date field.

 

[dwayne dibley]

The following advice is making certain assumptions. For example, your library has 4 copies of Tom Sawyer. Each copy has a unique ASC. If the copies share the same ASC, you will have to go into a much more complicated procedure to find the right book to enter as returned.

You will need to do the following:
1 Create a search form. Use Design view not the wizard. Place 1 unbound textbox on the form. Name the form BookReturnSearchForm

2. Create a query using all of the fields in your table. Name it BookReturnSearchQuery. In the criteria of the field ASC enter [Forms]![BookReturnSearchForm]![Text1] Where Text1 is the number of the text box on the BookReturnSearchForm.
In the criteria of the field ReturnedDate enter =0 (zero)

3. Create a Book Return form using BookReturnSearchQuery as the source (you may use the wizard to create this form). Name it BOOKRETURNFORM. Enter all the fields you want on the form which concern the book's return. On the bottom of the form enter 2 command buttons. The first closes the form, the second is labelled "RETURNED" (no quotes).

4. Go back to BookReturnSearchForm. Enter 2 command buttons at the bottom of the form. The first closes the form with no action taken. The second should be labelled SEARCH. Set it to open the form BOOKRETURNFORM.

When you enter an ASC in the search form and then click on search, the return form should open with the book you want on the screen. We want a loan with no return date or we will get a listing of all of the loans of that book that are in the database. This way we only get a listing of the outstanding loans for that book. I am using a blank return date field as an indicator that the book is out on loan.

5. With BOOKRETURNFORM in design view, right click the command button Returned. Select Build Code. In the top right corner of the screen there is a combo box. Click the down arrow and select On Click. In the blank area between the existing text, but just above "End Sub" enter: ([ReturnedDate] = Now()). This will enter todays date into the return date field.

Yes, thats how I would like it to work. And what you advised makes perfect sense.................................................but I cant get it to work


After I enter the ASC key and clicked the search button, it returns a blank form(no text boxes etc).

Thanks for advice, I will keep on and hoepefull get it resolved shortly.

 

[statsman]

I assume you are using test data.
Make sure the ASC you enter corresponds to a blank return date field.
Remember, the query we are using requires TWO criteria, the ASC and a BLANK return date

 

[dwayne dibley]

I assume you are using test data.
Make sure the ASC you enter corresponds to a blank return date field.
Remember, the query we are using requires TWO criteria, the ASC and a BLANK return date

The blank return date seems to be causing the problem. The form works If I dont use "=0"as the criteria for returned date. This is obviously not ideal as it will return historic loans of this item

Thanks for the help.

 

[statsman]

OOPS
I bet you have the returndate field set to date format.

Try the returndate = " " (one blank space) in the query.