Kamis, 07 September 2017

Re: [MS_AccessPros] Missing Data in SQL Servier backend

 

No.  To the left of the equals sign you are referencing the Form object inside the subform control.  To the right of the equals sign, you're referencing the Forms collection.


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Sep 7, 2017, at 4:53 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

I just noticed another question. the line that has the following below. Should the word Forms be Form?? 

Me.sfrmTaskDetails.Form!TaskID = Forms!frmTasks!TaskID

Jim Wagner


On ‎Thursday‎, ‎September‎ ‎07‎, ‎2017‎ ‎07‎:‎45‎:‎07‎ ‎AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


Jim-


I see no reason why it thinks you're trying to invoke Compact/Repair.

I would make these changes:

Me.sfrmTaskDetails.Form!TaskUpdateDate.SetFocus
Me.sfrmTaskDetails.Form!TaskUpdateDate = Date
Me.sfrmTaskDetails.Form!TimeSpent = 1
Me.sfrmTaskDetails.Form!TaskUpdate = "Used PeopleSoft Datawarehouse Database to transfer all needed tables and to copy into all working Databases. Also Processed the Repurpose Database objects."
Me.sfrmTaskDetails.Form!TaskID = Forms!frmTasks!TaskID
' Move focus back to the outer form to force subform record save.
Me.cboTaskType.SetFocus


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France)




On Sep 7, 2017, at 4:23 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

So I added the focus code but I get a new error.

You cannot compact the open database by running a macro or Visual Basic code.
Instead of using a macro or code, click the file tab and then click compact and repair database.

Not sure where the compact and repair is coming from. below is my code. Is there anything that I am missing?

Private Sub cmdTableTransfer_Click()

Rank.Value = "A"
Me.cboTaskType = 10
Me.cboTaskName = "Transfer Copy Process"
TaskTitle.Value = "Table Transfer"
TaskDescription.Value = "Table Transfer and Copy Processes for all Databases"
Me.cboDepartment = 33
DueDate.Value = Date
Me.ColorOfRecord = "vbRed"
CompletedDate.Value = Date
StatusType.Value = "Completed"
DoCmd.RunCommand acSaveRecord
Me.sfrmTaskDetails!TaskUpdateDate.SetFocus
Me.sfrmTaskDetails.Form!TaskUpdateDate = Date
Me.sfrmTaskDetails.Form!TimeSpent = 1
Me.sfrmTaskDetails.Form!TaskUpdate = "Used PeopleSoft Datawarehouse Database to transfer all needed tables and to copy into all working Databases. Also Processed the Repurpose Database objects."
Me.sfrmTaskDetails.Form!TaskID = Forms!frmTasks!TaskID
DoCmd.RunCommand acSaveRecord
Me.Refresh
Me.cmdOpenqryTodayTasksTimeSpent.SetFocus

End Sub



Jim Wagner


On ‎Thursday‎, ‎August‎ ‎31‎, ‎2017‎ ‎11‎:‎12‎:‎23‎ ‎AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



You need to do more one before you start populating the subform.  If your code populates all the fields you 
need on the subform, you need to move focus to a control on the subform and then back
to the main form to force the subform to save.  I don't think another acCmdSaveRecord will 
do it. 

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out 
Microsoft Office Access 2007 Inside Out 
http://www.viescas.com/

On Aug 31, 2017, at 7:09 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,
Thank you
But I was a little over zealous as you see in the code with the docmd.save. I have it in 2 places. where is the best place to put that? at the end or before the subform fields are populated?



Jim Wagner



On ‎Thursday‎, ‎August‎ ‎31‎, ‎2017‎ ‎10‎:‎03‎:‎10‎ ‎AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Ah, I see the problem.  DoCmd.Save saves the FORM,  not the record.  You need DoCmd.RunCommand acSaveRecord. 


John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out 
Microsoft Office Access 2007 Inside Out 
http://www.viescas.com/

On Aug 31, 2017, at 5:54 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

John,

for kicks and giggles I added a join in the Relationships diagram tool in Access today after the failed attempt..  1 to many

Yes it is a subform on a form.
frmTasks is the main form
sfrmTaskDetails is the subform

The code behind the button that adds the record

Private Sub cmdTableTransfer_Click()

Rank.Value = "A"
Me.cboTaskType = 10
Me.cboTaskName = "Transfer Copy Process"
TaskTitle.Value = "Table Transfer"
TaskDescription.Value = "Table Transfer and Copy Processes for all Databases"
Me.cboDepartment = 33
DueDate.Value = Date
Me.ColorOfRecord = "vbRed"
CompletedDate.Value = Date
StatusType.Value = "Completed"
DoCmd.Save
Me.sfrmTaskDetails.Form!TaskUpdateDate = Date
Me.sfrmTaskDetails.Form!TimeSpent = 1
Me.sfrmTaskDetails.Form!TaskUpdate = "Used PeopleSoft Datawarehouse Database to transfer all needed tables and to copy into all working Databases. Also Processed the Repurpose Database objects."
Me.sfrmTaskDetails.Form!TaskID = Forms!frmTasks!TaskID
Me.Refresh
DoCmd.Save
cmdOpenqryTodayTasksTimeSpent.SetFocus

End Sub



the link  master TaskID

the link Chidl    Taskid




Jim Wagner



On ‎Thursday‎, ‎August‎ ‎31‎, ‎2017‎ ‎08‎:‎42‎:‎46‎ ‎AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Jim- 


I would need to see any code you have in the form(s) you're using to enter the data.  Is this a form with a subform?

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
http://www.viescas.com/ 
(Paris, France)




On Aug 31, 2017, at 5:27 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

I added the save command and it did no help.


TaskId is an Autonumber in the tblTasks table

TaskDetailsID is an autonumber in the tblTaskDetails table
TaskID is a number in the tblTaskDetails table.

My frustration is that I can manually add the data for the task and the fields populate correctly. But when I run the procedure, it fails.

Jim Wagner



On ‎Monday‎, ‎August‎ ‎07‎, ‎2017‎ ‎10‎:‎14‎:‎18‎ ‎AM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



Thank You John, I added a save command to save the record. I do not want to mess up the sync of the numbers in the parallel databases so I will wait until tomorrow to see if it works. I will let you know. 

Jim Wagner



On ‎Monday‎, ‎August‎ ‎07‎, ‎2017‎ ‎09‎:‎00‎:‎04‎ ‎AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


 

Jim-

If "TaskID" is an "autonumber," that's the problem.  When you "dirty" a new row that has an Autonumber in Access, Access immediately sets the next value.  When the table is in SQL Server, however, SQL Server doesn't generate the number until after you save the row.

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
http://www.viescas.com/

On Aug 7, 2017, at 10:19, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:


Hello all,


I run a parallel Tasks local database and a SQL backend to Access database. On the databases I have a main form and a linked sub form. Every morning I run a process to get outside data into the database. Because I do it every day I have a button that populates fields in the form so I do not have to type them. This button works great on the local database but the SQL backend I am missing the data in the field that the forms are linked on the following below. If I add a new record by typing all of the fields I have no issues. But the populated process seems to miss the linked field. Every day I need to go to the table and add the Task ID from the main table to the table for the subform.

Is this a SQL issue or should I add the Taskid field to the vba process?


the link  master TaskID

the link Chidl    Taskid


the process


Private Sub cmdTableTransfer_Click()

Rank.Value = "A"
Me.cboTaskType = 10
Me.cboTaskName = "Transfer Copy Process"
TaskTitle.Value = "Table Transfer"
TaskDescription.Value = "Table Transfer and Copy Processes for all Databases"
Me.cboDepartment = 33
DueDate.Value = Date
Me.ColorOfRecord = "vbRed"
CompletedDate.Value = Date
StatusType.Value = "Completed"
Me.sfrmTaskDetails.Form!TaskUpdateDate = Date
Me.sfrmTaskDetails.Form!TimeSpent = 1
Me.sfrmTaskDetails.Form!TaskUpdate = "Used PeopleSoft Datawarehouse Database to transfer all needed tables and to copy into all working Databases. Also Processed the Repurpose Database objects."
Me.Refresh
cmdOpenqryTodayTasksTimeSpent.SetFocus

End Sub


Thank You for any advice. I am trying to learn SQL by creating the SQL backend scenario.


Jim Wagner










__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (13)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)