Replies

Can you get sample excel spreadshhet from your solution above?

Attached Files 2013-09-10 11:07

File Type: xlsm support4john LYC_Member_Data_Summary-1_MS_20130909_Test.xlsm (33.9 KB, 2 views)

Wow, thanks for this, worked great, just had to change the following:

Cells(lCurRow, 3).Value = shtCur.[K3].Value ‘FirstName

I like the way you parsed the csz, normalizing to lowest level

After further review and working with a secretary who doesn’t want to give up Excel

How would I create a form similar to the member tab, so when I click a member in the Summary page the form would open with
that members information displayed?

I have the form button in the tool bar

When I click on a member Agnello and form button the form displays the first member Adams

What kind of look-up vba code can I use to click on member Agnello and have member Agnello display in the form?

I goggled Excel form with row look-up without success

I understand it is an abortion

Can this be imported into a new Excel sheet, so I can have one row/record per member so he can stille work on it in a list format and I will work on bringing the excel file into a Access mdb?

Not to worry about the field descriptions

This is not my design/approach I inherited this from my club secretary

The real file has about 200 records

one direction, e.g. Detail to Summary that could be done with formulas would be great

.

1. With the report on the screen, you right click on the document

2. Click Export

3. Click Word RTF File

I’m looking for the equivlent DoCmd.RunCommand ????????? to put the on screen report into Office Links Word?

Thanks for the references

I have a customiezed Menu Bar that has most of the functions in the standard File, Edit, Insert, Tools, Windows and Help Menu Bar

And I have a mod_Tool_Bar_Menu module that has all the equivelent functions

Function Menu_FormView()

DoCmd.RunCommand acCmdFormView

End Function

What would be the equivlent DoCmd.RunCommand ????????? to put the on screen report into Office Links Word?

Thanks John

After further review IT guy at the site found the solution here:

PROBLEM – 02/25/13

When opening fe mdb

Error: 3044: ‘F:ClerkClerk_Be.mdb’ is not a valid path. ezy_Session Form_Open

SOLUTION – 03/09/13

How to make elevated programs recognize network drives
http://www.winability.com/how-to-make-elevated-programs-recognize-network-drives/

The FE Clerk.mdb is in C:NbsA2kFe

I’m familiar with trusted locations in Access 2007 & 2010

I wasn’t aware of trusted locations using Access 2003

What do I check for?

Thanks for information

After further review I replaced comctl32.ocx from another working computer and all is well on troubled computer so far

Can you tell me something about comctl32.ocx?

My working comctl32.ocx is dated 6/24/1998 596KB ActiveX control

When installing Access (any version 2000-2010) over the past 12 years, I would simply copy comctl32.ocx from a working computer to C:WindowsSystem32 and all was well

I there a more current version of comctl32.ocx I should be using? if so, where do I get it on the Internet?

Thanks for all your help

The customer has installed:

Microsoft Office Home and Student 2010 and Microsoft Office Professional Edition 2003

I ran it again after your suggestion and got a little further
?SaveReferences(“Clerk.ref”)
VBA: C:Program Files (x86)Common FilesMicrosoft SharedVBAVBA6VBE6.DLL
Access: C:Program Files (x86)Microsoft OfficeOFFICE11MSACC.OLB
stdole: C:WindowsSysWOW64stdole2.tlb
DAO: C:Program Files (x86)Common FilesMicrosoft SharedDAOdao360.dll
VBIDE: C:Program Files (x86)Common FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB
Office: C:Program Files (x86)Common FilesMicrosoft SharedOFFICE11MSO.DLL
Word: C:Program Files (x86)Microsoft OfficeOffice14MSWORD.OLB
TILT12Lib: C:Program Files (x86)Tilt12Tilt12.dll
ADODB: C:Program Files (x86)Common FilesSystemadomsado21.tlb

Choked on:
Microsoft Windows Common Controls 5.0 (SP2)
C:WindowsSystem32comctl32.ocx (date & time stamp 6/24/1998 596KB ActiveX control)

C:WindowsSystem32comctl32.ocx is there

Tried to regsvr32 C:windowssystem32COMCTL32.OCX and got:

The module “comctl32.ocx” failed to load

Where can I download a valid copy of “comctl32.ocx” that works with my setup?

Thanks for the response, I ran it again with your suggestion and got the following:

?SaveReferences(“Clerk.ref”)
VBA: C:Program Files (x86)Common FilesMicrosoft SharedVBAVBA6VBE6.DLL
Access: C:Program Files (x86)Microsoft OfficeOFFICE11MSACC.OLB
stdole: C:WindowsSysWOW64stdole2.tlb
DAO: C:Program Files (x86)Common FilesMicrosoft SharedDAOdao360.dll
VBIDE: C:Program Files (x86)Common FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB
Office: C:Program Files (x86)Common FilesMicrosoft SharedOFFICE11MSO.DLL

with Print #1, Ref.Name; “;”; Ref.FullPath highlighted in yellow
ComctlLib

Ref.Name = ComctlLib

Looks like it choked on the following reference:

Microsoft Windows Common Controls 5.0 (SP2)
C:WindowsSystem32comctl32.ocx (date & time stamp 6/24/1998 596KB ActiveX control)

Microsoft Word 14.0 Object Library
C:Program Files (x86)Microsoft OfficeOffice14MSWORD.OLB

Tilt 1.2 MultiLabel Type Library
C:Program Files (x86)Tilt12Tilt12.dll

Microsoft ActiveX Data Objects 2.1 Library
C:Program Files (x86)Common FilesSystemadomsado21.tlb

Any help is welcomed

Thanks ruirib, you solved my problem, now I can proceed further on the Access 2010 minefield

I now notice that this only is a problem on reports in which the RecordSource is changed in the Report_Open event.

It appears that when the RecordSource is changed in Report_Open, the Filter property, which would normally receive the WhereCondition doesn’t “stick”. All records are retrieved for the report instead of the desired 1 filtered record

This seems to be a problem with Access 2010 only after SP 1 is applied

In Office 2010 SP1, the OpenReport method loses WhereCondition
http://answers.microsoft.com/en-us/office/forum/office_2010-access/in-office-2010-sp1-the-openreport-method-loses/9d76bb3d-d942-4335-ac2f-aead50887916

Description of the Access 2010 hotfix package (Access-x-none.msp): October 25, 2011
http://support.microsoft.com/kb/2596496
Microsoft Access 2010 (14.0.6024.1000) SP1 MSO (14.0.6112.5000) after SP1
Microsoft Access 2010 (14.0.6111.5000) SP1 MSO (14.0.6112.5000) after hotfix

Looks like the problem is solved for now by inserting If Me.Dirty Then Me.Dirty = False

Code has worked OK for the past 12 years without the command on 2000, 2002, 2003 and 2007 systems

Is this considered a MS Access bug or a programming error I got away with for 12 years?

Private Sub Add_Record_Click()

gloSetValue “FinalizeButtonClicked”, “Add”
gloSetValue “POSButtonClicked”, “Add”
Me.ButtonClicked = gloGetValue(“POSButtonClicked”)
mstrButtonClicked = “Add”
cboLookupSequence.SetFocus
FormState (“Unlocked”) ‘ Locked or Unlocked Me.strOwnerName.Locked = False
ControlState (“Edit”) ‘ Normal or Edit Me.strOwnerName.Enabled = True

strLookupCriteria = Null
DoCmd.GoToRecord , , acNewRec

Me.strProfileCode = GetPref(“Profile Code”) ‘ this forces autonumber in master record tbl_RevTran_Master

‘ the following command solved the problem
If Me.Dirty Then Me.Dirty = False

Me.frm_POS_Detail.SetFocus
Me.frm_POS_Detail.Form.strTranDesc.SetFocus
Me.frm_POS_Detail.Form.strTranDesc = Null
Me.frm_POS_Detail.Form.strTranCode2.SetFocus

End Sub

In the second field in tbl_RevTran_Detail I have:

If Me.Dirty Then
DoCmd.RunCommand acCmdSaveRecord ‘to save detail
End If

Also as a test I did the following:

If I just click Add and at step 6

if I press Esc Key once the detail record is cleared tbl_RevTran_Detail, lngRevTranID returns to AutoNumber which is OK

If I click Esc Key a second time tbl_RevTran_MasterlngRevtranID returns ti AutoNumber, on 2000, 2002, 2003 and 2007 systems this is saved and the numerical AN stays

Any other thoughts