Open Db from a Db then pass values (1 Viewer)

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
I found some code to open another database off of a button. I have gotten to a point that I can open a form within the target database and open it in 'new record' status. Next I would like to pass some data between the two databases and am looking for some help with that. Any help is appreciated.

' Procedure : OpenDb
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Open another database
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: Uses Late Binding, so none required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sDb : Fully qualified path and file name with extension of the database to
' open
'
' Usage:
' ~~~~~~
'Call OpenDb("C:\Users\Daniel\Documents\Database25.accdb")
'
' Revision History:
' Rev Date(yyyy/mm/dd) Description
' **************************************************************************************
' 1 2014-11-07 Initial Release
' 2 2018-01-21 Error handling updated for posting on website
'---------------------------------------------------------------------------------------
Public Function OpenDb(sDb As String)
On Error GoTo Error_Handler
'Early binding
'Use the following line if being used in Access or using Access reference
' provides intellisense!
Dim oAccess As Access.Application
'Late binding
'Use the following line if being used outside of Access without an Access reference
' Dim oAccess As Object

Set oAccess = CreateObject("Access.Application") 'Create a new Access instance
With oAccess
.OpenCurrentDatabase sDb 'Open the specified db
.Visible = True 'Ensure it is visible to the end-user
.UserControl = True
.DoCmd.OpenForm "frmDocDetail", , , , acFormAdd


' .DoCmd.OpenForm "YourFormName" 'Open a form?
' .DoCmd.RunMacro "YourMacroName" 'Run a Macro?
End With

Error_Handler_Exit:
On Error Resume Next
If Not oAccess Is Nothing Then Set oAccess = Nothing
Exit Function

Error_Handler:
MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: OpenDb" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occurred!"
Resume Error_Handler_Exit
End Function

Private Sub CmdPRR_Click()
' code to open Quik Docs
' Call function to open database - above
Call OpenDb("C:\Users\gkissick\Desktop\QuikDoc locals\Quik Doc Track GaryV112719.21.accdb")

' code to create the PRR form
' MsgBox "Preparing PRR Form"
Dim oXL As Object
Dim NewFile As String
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set wb = oXL.Workbooks.Open("C:\Users\gkissick\Desktop\PRR.xlsx", True, False)

wb.sheets(1).Range("F2").Value = "YES"
wb.sheets(1).Range("h2").Value = Forms!frmMRRLog!DocReference
wb.sheets(1).Range("c4").Value = Forms!frmMRRLog!SupplierName
wb.sheets(1).Range("h4").Value = Date
wb.sheets(1).Range("c6").Value = Forms!frmMRRLog!item
wb.sheets(1).Range("k17").Value = Forms!frmMRRLog!ProblemDescription
wb.sheets(1).Range("e6").Value = Forms!frmMRRLog!mrr_num
wb.sheets(1).Range("h2").Value = "PRR-" & Year(Date) & "-" & Forms!frmMRRLog!mrr_num
NewFile = "C:\Users\gkissick\Desktop\PRR " & Forms!frmMRRLog!mrr_num
wb.SaveAs FileName:=NewFile, FileFormat:=56
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Data are stored in tables, so maybe you could just manipulate the table instead of the form. Just a thought...
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
I wanted to pass a few values then have the user fill in the rest of the form and this is always a new form. So its not just a matter of adding / changing data in the table. Although I did have the thought to first create a new entry into the table in the target db with the 'few values' then change the code to open the target db, open the form then move to the recently added record. I think that would have the same result. Can you remind me the code to push data into a table in another database? Thank You
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
I wanted to pass a few values then have the user fill in the rest of the form and this is always a new form. So its not just a matter of adding / changing data in the table. Although I did have the thought to first create a new entry into the table in the target db with the 'few values' then change the code to open the target db, open the form then move to the recently added record. I think that would have the same result. Can you remind me the code to push data into a table in another database? Thank You
Hi. There's lots of ways to do that. One way is to use an APPEND query. Something like:
Code:
INSERT INTO TableName IN "c:\foldername\databasename.accdb" (FieldName) VALUES (SomeValue)
Hope that helps...
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
I will try it. Am I making a safe assumption that it is better to do this in the back end database rather than call the linked table that is in the front end ?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
I will try it. Am I making a safe assumption that it is better to do this in the back end database rather than call the linked table that is in the front end ?
Hi. Either way you do it, you are still "calling" the same thing/object. The data is only stored in one location, so Access will somehow get there. So, it probably doesn't matter which way you go. Just my 2 cents...
 

Dreamweaver

Well-known member
Local time
Today, 18:47
Joined
Nov 28, 2005
Messages
2,466
I would do it the way theDbGuy suggests the other would involve creating a library database and adding references/code Etc using the tables and creating a local form would be a lot simpler.


mick
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
Tried to add code to make a new record with a few pieces of 'dummy data'. It ran but then I opened the table that was supposed to have the new record and I didn't see anything new. Good news.. No errors though!! Am I missing something? I found the code in MS development, looked pretty similar to yours. .. INSERT INTO...

Private Sub CmdPRR_Click()
' code to create a new PRR record from MRR database
' just dummy data for now
Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("\\Lbcixs1\lbi1_d\deptqa\Suppliers\Quik Docs\IMPORTANT FILES DO NOT OPEN\Quik Doc NEW Track Back.accdb")

' Create a new record in the Employees table. The
' first name is Harry, the last name is Washington,
' and the job title is Trainee.
dbs.Execute " INSERT INTO tblDocsIssued " _
& "(PartNum,SQEName) VALUES " _
& "('4444444','Gary Kissick');"




' code to open Quik Docs
' Call function to open database - above
Call OpenDb("C:\Users\gkissick\Desktop\QuikDoc locals\Quik Doc Track GaryV112719.21.accdb")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
Hi. Try adding some more lines and options. For example:
Code:
...
dbs.Execute "INSERT INTO...
&...
&...');"[color=red], dbFailOnError
MsgBox dbs.RecordsAffected & " records inserted"[/color]
...
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
Hi. Try adding some more lines and options. For example:
Code:
...
dbs.Execute "INSERT INTO...
&...
&...');"[COLOR=red], dbFailOnError
MsgBox dbs.RecordsAffected & " records inserted"[/COLOR]
...

Runtime 3314 .. you must enter a value in the 'tblDocsIssued.DocType' field.

… Looks like it doesn't understand that tblDocsIssued is the name of the table that I want to add the record to.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
Runtime 3314 .. you must enter a value in the 'tblDocsIssued.DocType' field.

… Looks like it doesn't understand that tblDocsIssued is the name of the table that I want to add the record to.
It sounds more like to me that DocType is a required field, which means you'll have to include it in your SQL statement and assign a value to it.
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
It sounds more like to me that DocType is a required field, which means you'll have to include it in your SQL statement and assign a value to it.

Yes, so it looks like I need to assign something (or null) for each field. so the real value comes from being able to assign those based on controls on the form my button was on. At this point I am hoping I can use Me.DueDate with DueDate being the name of a textbox on the form. I'll try and see
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
Yes, so it looks like I need to assign something (or null) for each field. so the real value comes from being able to assign those based on controls on the form my button was on. At this point I am hoping I can use Me.DueDate with DueDate being the name of a textbox on the form. I'll try and see
Hi. If it's a "required" field, you won't be able to assign Null to it. Maybe you could try opening the other db manually, try to create a query in there and then test it. Once you get it to work, you can copy the SQL statement into your code.
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
OK. Marginal progress. I got it to work using the 'dummy' data.

dbs.Execute " INSERT INTO tblDocsIssued " _
& "(DocType,PartNum,AssignedVendor,ProjNum,SQEName) VALUES " _
& "('PRR','U5JJJJ','BestVendor','5555','Gary Kissick');", dbFailOnError

I have tried Me.mrr_num and even Forms!frmMRRLog!mrr_num but it doesn't recognize it and tells me I don't have enough entries. i'm missing something on the syntax.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
OK. Marginal progress. I got it to work using the 'dummy' data.

dbs.Execute " INSERT INTO tblDocsIssued " _
& "(DocType,PartNum,AssignedVendor,ProjNum,SQEName) VALUES " _
& "('PRR','U5JJJJ','BestVendor','5555','Gary Kissick');", dbFailOnError

I have tried Me.mrr_num and even Forms!frmMRRLog!mrr_num but it doesn't recognize it and tells me I don't have enough entries. i'm missing something on the syntax.
Would you kindly show us exactly how you tried it? Thanks.
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
dbs.Execute " INSERT INTO tblDocsIssued " _
& "(DocType,PartNum,AssignedVendor,ProjNum,SQEName) VALUES " _
& "('PRR',me.item,'BestVendor','5555','Gary Kissick');", dbFailOnError
MsgBox dbs.RecordsAffected & " records inserted"
'example of ways to insert data from the form.
' & "('PRR',Me.item,Forms!frmMRRLog!SupplierName,'5555','Gary Kissick');", dbFailOnError
'MsgBox dbs.RecordsAffected & " records inserted"
' The first field will always be PRR and the last field will always be Gary Kissick (That's me) so they are fine.
' the middle three are sitting on the form in controls so they SHOULD be easily accessed (pun?)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
dbs.Execute " INSERT INTO tblDocsIssued " _
& "(DocType,PartNum,AssignedVendor,ProjNum,SQEName) VALUES " _
& "('PRR',me.item,'BestVendor','5555','Gary Kissick');", dbFailOnError
MsgBox dbs.RecordsAffected & " records inserted"
'example of ways to insert data from the form.
' & "('PRR',Me.item,Forms!frmMRRLog!SupplierName,'5555','Gary Kissick');", dbFailOnError
'MsgBox dbs.RecordsAffected & " records inserted"
' The first field will always be PRR and the last field will always be Gary Kissick (That's me) so they are fine.
' the middle three are sitting on the form in controls so they SHOULD be easily accessed (pun?)
Well, if you want to resolve a form reference, then you'll have to keep them outside of the String variable. For example:
Code:
...
& "('PRR','" & Me.Item & "','" & Me.SupplierName & "',...
However, you will have to test which one works, using Me. or Forms!
 

gakiss2

Registered User.
Local time
Today, 11:47
Joined
Nov 21, 2018
Messages
168
Thank You, that did it.

one of the fields has an 'after update' that doesn't fire when the data is put directly into the table instead of entered through the form. I'll have to figure out how to do that because that kicks off a procedure that assigns a tracking number. but i'ts almost 4 and I am done for today. Thank You again for all your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:47
Joined
Oct 29, 2018
Messages
21,358
Thank You, that did it.

one of the fields has an 'after update' that doesn't fire when the data is put directly into the table instead of entered through the form. I'll have to figure out how to do that because that kicks off a procedure that assigns a tracking number. but i'ts almost 4 and I am done for today. Thank You again for all your help.
Hi. You're welcome. Good luck with the rest of your project. Good night!
 

Users who are viewing this thread

Top Bottom