command to update record from data in other table

[ianking]

I have two tables - books and borrowers

Books has a borrowerID field as does Borrowers (linked by a relationship) BorrowerID is unique in the borrowers table

On the form where a borrower enters their details I have a button which opens a form displaying the results of a query for that barcode. This query is called borrower_barcode

I'd like to add a button to the borrower_barcode query form which will make borrowers.borrowerID = books.borrowerID for the record found by the borrower_barcode query.

Can anyone help me with the code for this button?

many thanks,

Ian

 

[ajetrumpet]

You might be able to adopt a version of the same syntax that is found here:
http://www.access-programmers.co.uk/forums/showthread.php?t=134066

 

[ianking]

using data from a text box to update a form

Sorry -I couldn't adapt that bit of code successfully.

my tables and fields would be

books.borrowerID and borrower.borrowerID

however the data I need to identify the record in the books table is entered by the user on a form displaying the data from the borrower table, but has a text box where they enter the barcode.

Both borrower.borrowerID and the barcode are displayed in text bos#xes on the form

on scanning the barcode, what would be the code I should add to the

barcodetext_AfterUpdate() procedure?

(or alternatively on the cmdborrow_Click() procedure if I use a button)


many thanks,

Ian

 

[ajetrumpet]

you may find this hard to believe Ian, but I'm having a difficult time understanding what you're doing here.

You want to assign one of the values that pop up in those textboxes to a table record, right? You've really got me confused here...

 

[ianking]

Hopefully a clearer explanation

I think that I've got it clear in my head what I want to be able to do - I think it should be possible.....


The application (as you have probably guessed) is a library database with two tables - borrowers and books. The tables are linked with a relationship where the borrowers.borrowerID is unique, but the books.borrowerID is not (a borrower can have more than one book out)

I need to update the borrowerID field in the books table when a user enters a barcode corresponding to that book on a form.

The user either enters their details if they are new (or finds them if they are not) by entering them on a user entry form. A borrowerID is generated automatically for them and is displayed on that form in a text box. Also on this user entry form is a text box where they can enter the barcode of the book they wish to take out.

How can I use the value of the barcode text box on this form to identify the appropriate record in the books table and update its borrowerID field with the borrowerID text box value

thanks for your patience,

regards,

Ian

 

[ajetrumpet]

How can I use the value of the barcode text box on this form to identify the appropriate record in the books table and update its borrowerID field with the borrowerID text box value

I have no idea how you identify the book associated with the bar code that you see in the textbox. I have never worked with barcodes, so I don't know anything about the input value that you see when you scan a barcode (if this is what you mean).

As far as updating the borrowerID in the books table with the borrowerID that you see in your form's text box, that's easy. Just do this:

On Click (or whatever)

  docmd.runsql "UPDATE booksTable SET bookstable.borrowID = " &  _ 
     forms!yourForm!borrowIDControl & " WHERE bookstable.bookName = " & _ 
        forms!yourform!ControlThatListsTheBookIdentifier"

thanks for your patience

No no, thanks for your patience. I'm the one confused here...

 

[ianking]

Got it now

Many thanks - that did the trick!

(any idea how I suppress the "You are about to update 1 row etc.." message)

regards,

Ian

 

[ajetrumpet]

(any idea how I suppress the "You are about to update 1 row etc.." message)

use command:

docmd.setwarnings false/true

 

[ianking]

thanks again - happy now