setting text box data to table value

[mcsheff]

setting text box data to table value??

my problem: I want a text box on a form (that contains a value from a table) to put the value it displays into another table, when a button is clicked

here is an example of the tables:

USERS

UserID
First name
Surname
Address

I want UserID on click to be placed in:

Loans

UserID

I think what I want code wise is something like the following:

Private Sub submit_Click()
On Error GoTo Err_Borrow_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Booksearch"
DoCmd.OpenForm stDocName, , , stLinkCriteria

UserID(in loans table) = (the current value of the text box userID, which is from users table)

Exit_Borrow_Click:
Exit Sub

(Obviously the bits in brackets are the bits I dont know the code for )

I dont know if I have explained myself very well. I hope someone can help.

 

[mcsheff]

Ive spoken to someone who says that I need to define some variables, and that I cant just pass the value on click to the other table. I think you can but as you can tell Im no expert

Any help would be greatly appreciated as Im sat at work trying to look busy, although Im totally stuck

Thanks in advance

 

[Mile-O]

Firstly, what are the fields in the Loans table?

What is that table's primary key?

 

[mcsheff]

The loans table will contain:

BookID (primary key)
UserID

as bookID is unique.

(UserID may be repeated as a user can borrow many books)

Cheers

 

[Mile-O]

Okay, so on your form, you have a combobox.

The combobox's RowSource is set to:

SELECT UserID, [First name] & " " & Surname AS User FROM USERS ORDER BY [First name] & " " & Surname;

The Column width is set to: 0cm
The ColumnCoubt is set to: 2
The BoundColumn is set to: 1

 

[mcsheff]

errr I dont have a combo box? Do you mean I need to put one on??

All I have atm is text boxes and labels and a search function. once the user has found the details they want. I want them to click a submit button which will send the current UserID ID to the loans table.

This process I thought would then be repeated on the next form, for the BookID.

 

[Mile-O]

errr I dont have a combo box? Do you mean I need to put one on??

Wouldn't it be easier?

But, anyway, you are the one who wants this so, which version of Access are you using? 97 or above?

 

[mcsheff]

Ahhh now I see what u mean
ill stick with my text boxes atm as I dont want to cause myself more problems

Im using Access 2000 (on a darn 486 with a tiny monitor )

 

[Mile-O]

Something like this...on the click of your button?

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "Loans", cn, adOpenDynamic, adLockOptimistic
    
    With rs
        .AddNew
        .Fields("UserID") = Me.txtUserID
        .Update
        .Close
    End With
    
    Set rs = Nothing
    Set cn = Nothing

 

[mcsheff]

eeep

Ok mate ill have a look at that and try and figure it out

Sorry about my lack of access knowledge I havent touched it since uni and now suddenly Im expected to remember what I did!

Cheers again for your help. (im sure ill be back posting in a mo )

 

[Mile-O]

It basically creates a recordset object (your table Loans) and puts the value of a textbox - in this case, txtUserID - into your table, and then destroys the recordset and connection object.

 

[mcsheff]

heres my edited version of the code:

Private Sub Borrow_Click()


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "Loan",table I want to enter data in cn, adOpenDynamic, adLockOptimistic

With rs
.AddNew
.Fields("BookID"column name from the books table ) = Me.BookID not quite sure about this bit?
.Update
.Close
End With

Set rs = Nothing
Set cn = Nothing


getting error something like " you cannot add record because a related field is required in the "users" table.

Is this because the "loan" table contains both bookID and userID??

The userID isnt set to required or anything like that.

Cheers

 

[mcsheff]

so the me.BookID is the name of the text box from where I want to get the data yeah?

But Im still getting funny error about the "users" table, even though thats not been used anywhere

 

[Mile-O]

Have a look at your relationships? Can any of them be messing it up?

Also, BookID is a BAD name for a texbox as it could (and will) be confused with the field of the same name. Consider changing its name to txtBookID to differentiate it.

 

[mcsheff]

yeah I noticed on the confusion front ill try and sort it Also the only relationships I have are:

"BookID" in the "books" table is related to the BookID in "Loan" table.

"UserID" in the "loan" table is related to the "userID" in the "users" table.

So I dont see what can be causing it

ill have a look when I get home.

Thanks for your help and ill be sure to let you know what happens

 

[mcsheff]

Thanks for your help people After a fair bit of head scratching and trial and error I finally got it work.

I now have a form where books can be returned. On the click of the return button I want the database to using the key bookID remove the related record from the loan table. I was thinking I can do this by something like the following: (not sure about the syntax etc?)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "Loan", cn, adOpenDynamic, adLockOptimistic

With rs
.Delete
.Fields("BookID") = Me.txtbox_containing_bookID
.Fields("UserID")
.Update
.Close
End With

Set rs = Nothing
Set cn = Nothing



DoCmd.Close

Thanks once again for all your help, and hopefully ill be finished soon

 

[mcsheff]

Thanks for your help people After a fair bit of head scratching and trial and error I finally got it work.

I now have a form where books can be returned. On the click of the return button I want the database to using the key bookID remove the related record from the loan table. I was thinking I can do this by something like the following: (not sure about the syntax etc?)

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "Loan", cn, adOpenDynamic, adLockOptimistic

With rs
.Delete
.Fields("BookID") = Me.txtbox_containing_bookID
.Fields("UserID")
.Update
.Close
End With

Set rs = Nothing
Set cn = Nothing



DoCmd.Close

Thanks once again for all your help, and hopefully ill be finished soon

 

[Mile-O]

Close, close

With rs 
    Do While Not rs.EOF
    If .Fields("BookID") = Me.txtbox_containing_bookID Then
        .Delete
        .Close
    Else
        .MoveNext
    End If
End With

or something like that....

 

[mcsheff]

Thanks mate you been a great help

 

[Mile-O]

No probs! Take it you saw that I missed out the Loop KeyWord?