Jim-
SOMETHING is calling acCompact. Or even the import didn't fix existing corruption. Can you rebuild the problem form from scratch and try that?
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 12, 2017, at 1:20 AM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I did and I still get the error that states
You cannot compact the open database by running a macro or Visual basic code.
Jim Wagner
On Monday, September 11, 2017 12:18:55 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Did you compile your VBA project after the import?
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 11, 2017, at 9:15 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
so I copied the objects and then linked the sql tables from sql server. But I am getting errors and the database seems really slow. I have to click refresh all for the main form to show up.
one of the errors is that my clock no longer works and a dlookup is giving me an #Error
When I try to run the button now I get an error and it stops on the below statement.
Me.sfrmTaskDetails!Form!TaskUpdateDate.SetFocus
Then I get a Type Mismatch error
This experiment is not going as planned.
Jim Wagner
On Saturday, September 09, 2017 12:43:52 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Jim-
It's best to copy only the local (non-linked) Access tables, then rebuild the links.
John Viescas, author
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
On Sep 9, 2017, at 01:38, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I have never done that with linked sql tables. will the links come across or will I need to recreate the links to sql server?
Jim Wagner
On Friday, September 08, 2017 02:49:19 PM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
That's beginning to stink of database corruption. Try creating a brand new empty database and importing all the objects from the old one.
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 8, 2017, at 9:31 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Oops it is F8
the compact comes on
Me.sfrmTaskDetails.Form!TaskUpdateDate = Date
But I put back the line that puts the focus on the control on the subform. and it went past the line and did not get the compact message but the subform is not populated. It still goes to the table but the id is still empty.
Me.TaskTitle.SetFocus
Me.Refresh
Me.Refresh
Jim Wagner
On Friday, September 08, 2017 12:17:15 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
I thought F5 was to step to the next line
Jim Wagner
On Friday, September 08, 2017 11:19:07 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Jim-
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 8, 2017, at 7:35 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Hi John,
- Linked field Properties
the source Object for the subform is sfrmTaskDetails
the Link Master Fields is TaskID
the link Child Fields is TaskId
the Filter On Empty master is set to Yes
the Enabled is set to Yes
the locked property is set to No
- VBA code
I changed the line to read from
Me.sfrmTaskDetails.Form!TaskID = Forms!frmTasks!TaskID
to read
Me.sfrmTaskDetails.Form!TaskID = Me.TaskID
- Compact and Repair message
After running the button I still get the compact error.
I did a search for compact and nothing showed up.
But it crashed on a new line
Me.sfrmTaskDetails!Form!TaskUpdateDate.SetFocus
But below the message box was the data in the subform. But when I clicked ok on the message box the data disappeared. it was in the table though like before but missing the id again
I put a Me. on the main form controls to make sure they are referencing the correct form.
- Breakpoint
Then I put a breakpoint on Me.Rank.Value = "A" and ran the procedure. Interesting things happened. the frmINProcessTasksMain form opened. then I hit F5 and the compact and repair message box came up again. the other form opens because I have some code that if the StatusType combo box after update = Completed then the form will open. But still no compact and repair code. My local database does not have this issue.
I checked the options for the database to see if Compact on close was checked and it is not.
I am at a loss for this. But then again I usually am at a loss.
Jim Wagner
On Friday, September 08, 2017 08:46:16 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Do you have the Link Master Fields and Link Child Fields of the subform set properly? With that set, you shouldn't have to do Me.sfrmTaskDetails.Form!TaskID = Forms!frmTasks!TaskID. And if this code is running in frmTasks, you should be using Me instead of Forms!frmTasks.
I suspect there's some code running elsewhere that is triggering the Compact / Repair. Open any module in the VB Editor, open the Find dialog, and search for the word Compact in "Entire Project".
If that doesn't work, put a halt on the first statement in your code, then step through the code one statement at a time to try to find out what is triggering it.
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 8, 2017, at 4:52 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
Jim Wagner
On Thursday, September 07, 2017 03:04:57 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John,
Okay. I changed it and I will let you know in the morning. I do not want to get the 2 databases out of sync. Thank You for your assistance.
Jim Wagner
On Thursday, September 07, 2017 08:20:14 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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:
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
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
On Aug 31, 2017, at 7:09 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
John Viescas, author
On Aug 31, 2017, at 5:54 PM, Jim Wagner luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
On Aug 7, 2017, at 10:19, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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
(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?
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
John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
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
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
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:
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
(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
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
On Aug 7, 2017, at 10:19, luvmymelody@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
__._,_.___
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 (27) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar