UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Add New Records Using If Joining 3 Tables In A Form?, Access 2010    
 
   
Ainnaa
post Dec 17 2019, 10:19 PM
Post#1



Posts: 4
Joined: 17-December 19



Hello,
Im having problem which is (run-time error '3251':
Object or provider is not capable of performing requested operation).

3 tables which is tblStaff, tblBorrow and tblDevices.
Basically, this form is to save record staff that borrow that device.
and that details records of borrow will display on listbox

This is my Code for button save,

Private Sub btnSave_Click()
Dim auto, autoS, autoD, dateB, staffN, StaffID, loc, typeD, Model, DeviceID, phoneNoUse, remarkB
Dim st As String

With rs
If Status = "New" Then
.AddNew
rs(1) = txtDateB.Value
rs(2) = txtAutoS.Value 'autonumber from tblStaff
rs(3) = txtAutoD.Value 'autonumber from tblDevices
rs(4) = txtRemarkB.Value
rs(8) = cboPhoneNo.Value
.Update

ElseIf Status = "Old" Then
auto = "'" & txtIDB.Value & "'"
autoS = "'" & txtAutoS.Value & "'"
autoD = "'" & txtAutoD.Value & "'"
dateB = "'" & txtDateB.Value & "'"
phoneNoUse = "'" & cboPhoneNo.Value & "'"
remarkB = "'" & txtRemarkB.Value & "'"

st = "Update tblBorrow Set DateofBorrow = " & dateB & ", IDs = " & autoS & ", ID = " & autoD & ", RemarkofBorrow = " & remarkB & ", UsingPhoneNo = " & phoneNoUse & " WHERE BorrowID = " & txtIDB.Value
DoCmd.RunSQL st
End If
End With

MsgBox ("New record has been successfully saved.")
Call Form_Load
End Sub


Appreciate if someone can help me
Thank you smile.gif
Go to the top of the page
 
June7
post Dec 17 2019, 10:33 PM
Post#2



Posts: 1,152
Joined: 25-January 16
From: The Great Land


You don't even show a recordset is declared and opened. How can you add record to a recordset that doesn't exist? That may be cause of error.

What line does error trigger on?

Why don't you just use bound form?

Might want to explore MS Lending Library database template.

For future, please post code between CODE tags to retain indentation and readability.
This post has been edited by June7: Dec 17 2019, 10:38 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Ainnaa
post Dec 17 2019, 11:40 PM
Post#3



Posts: 4
Joined: 17-December 19



I already declared recordset in Form_Load


Private Sub Form_Load()
DoCmd.Maximize
If cnn.State = adStateOpen Then cnn.Close
Set cnn = CurrentProject.AccessConnection

If rs.State = adStateOpen Then rs.Close
SQL = "SELECT * FROM tblBorrow ORDER BY DateofBorrow Desc"
With rs
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.ActiveConnection = cnn
.Open SQL, cnn, , , adCmdText
End With
Set lstBorrow.Recordset = rs
End Sub

This post has been edited by Ainnaa: Dec 17 2019, 11:41 PM
Go to the top of the page
 
projecttoday
post Dec 17 2019, 11:53 PM
Post#4


UtterAccess VIP
Posts: 11,438
Joined: 10-February 04
From: South Charleston, WV


welcome2UA.gif

Which line generates the error?

--------------------
Robert Crouser
Go to the top of the page
 
June7
post Dec 18 2019, 12:12 AM
Post#5



Posts: 1,152
Joined: 25-January 16
From: The Great Land


No, you have not declared recordset object variable in Load event. You simply start referencing this variable. Same for cnn variable.

Variable declaration requires a Dim statement.

Declaring a variable in one procedure does not make it available to another.

If you want a variable to be available to multiple procedures, variable must declared public or global in a module header.

You should have Option Explicit in the header of every module.

This post has been edited by June7: Dec 18 2019, 12:13 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
Ainnaa
post Dec 27 2019, 12:15 AM
Post#6



Posts: 4
Joined: 17-December 19



Sorry for late response. Thank you, June7 and projecttoday
I already declared that recordset and it does work smile.gif
Thank you
Go to the top of the page
 
projecttoday
post Dec 27 2019, 12:30 AM
Post#7


UtterAccess VIP
Posts: 11,438
Joined: 10-February 04
From: South Charleston, WV


Dim rs As DAO.Recordset

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Dec 27 2019, 05:21 AM
Post#8


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


With the code posted in Post#3, I think you would rather need:

Dim rs As ADODB.Recordset

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Dec 27 2019, 05:24 AM
Post#9


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


If you are still having problems, it is more likely with the options you have opened the Recordset with (probably cursor type: adOpenDynamic) Try adOpenKeyset instead.

--------------------


Regards,

David Marten
Go to the top of the page
 
June7
post Dec 27 2019, 05:55 AM
Post#10



Posts: 1,152
Joined: 25-January 16
From: The Great Land


Don't need to open a recordset just to set a listbox.

Me.lstBorrow.RecordSource = "SELECT * FROM tblBorrow ORDER BY DateofBorrow Desc"

Again, there is no recordset opened in the Click event.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
cheekybuddha
post Dec 27 2019, 06:47 AM
Post#11


UtterAccess Moderator
Posts: 12,064
Joined: 6-December 03
From: Telegraph Hill


Oops! You're right, June!

I'm guilty of seeing Robert's response and only skimming everything above. blush.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Dec 27 2019, 07:49 AM
Post#12


UtterAccess VIP
Posts: 11,438
Joined: 10-February 04
From: South Charleston, WV


That makes me think that maybe ainnaa is going about it the wrong way.

QUOTE
3 tables which is tblStaff, tblBorrow and tblDevices.


If a staff member is borrowing a device, you don't need to update 3 tables. You only need to update tblBorrow.

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Dec 27 2019, 09:20 AM
Post#13


UA Admin
Posts: 36,489
Joined: 20-June 02
From: Newcastle, WA


I agree. This approach appears to be going the long way round the block to get to the front porch.

For starters, we seldom see successful data ENTRY forms bound to queries joining multiple tables. The reason, I suspect, you've tried to resort to this approach to adding new records is due to that very hurdle.

I have a sample database on my website illustrating one typical approach to adding records in a junction table. Here the junction table is tblBorrow. It contains foreign keys from the two other tables, to record a "borrow" event in which one staff member borrows one device. I imagine it would have a DateBorrowed and a DateReturned field as well.

This demo is based on Musicians, Songs and Recordings, which parallel your Staff (Musician), Device (Song) and Borrowing (Recording).

The standard approach to such requirements is a main form and a subform, each bound to a SINGLE table or a query based on a SINGLE table. I blogged about this, with a fuller explanation. Spend some time familiarizing yourself with the method, then see if you can't simplify your own approach the same way.
This post has been edited by GroverParkGeorge: Dec 27 2019, 11:15 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Ainnaa
post Dec 30 2019, 02:56 AM
Post#14



Posts: 4
Joined: 17-December 19



Yes, you're right projecttoday. I just need to update tblBorrow only.
cheekybuddha, I already declared Dim rs As New ADODB.Recordset at initial code.
Thank you, June7 and GroverParkGeorge.
Dear all, thank you so much for helping me out. I already find the solution yayhandclap.gif

thanks.gif
Go to the top of the page
 
projecttoday
post Dec 30 2019, 05:56 AM
Post#15


UtterAccess VIP
Posts: 11,438
Joined: 10-February 04
From: South Charleston, WV


You're welcome.

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th January 2020 - 08:28 AM