Difficulties programming a command button

[CB]

Hi all,

I’m doing some database modification after being away from Access (and the
calibration database I created) for over six years. I’m looking at what I’ve
done and asking myself “How on earth did I do that???†Needless to say, I’m
forgetting an awful lot so please bear with me.

I’m trying to program a command button such that it will open an Excel
workbook to a specific spreadsheet depending on the equipment and serial
number displayed on the form. (This is only a temporary work around until I
get a chance to set this up in the database but we need something for the ISO
audit coming up.)

A little background…

The button is to be on main form whose record source is a query. The main
form displays the query results of equipment make and the serial number. When
scrolling through the records displayed on the main form, the sub-form will
display the associated calibration records for each piece of equipment
(record source is a table).

My goal …

When the user scrolls through and stops at the record for the “Fluke 45
DMMâ€, “serial number 123456â€, then clicks the “Get verifications†button, the
workbook will open to the spreadsheet for serial number 123456. If the user
then stops at “Fluke 179 DMMâ€, “serial number 98765â€, then clicks the button,
the workbook will open to the spreadsheet for serial number 98765. And so on.

I haven’t been able to get a macro to work and am thinking I must use code.
I did find code somewhere on this newsgroup to open a given spreadsheet but
now I need to modify it such that the particular sheet that opens is directly
related to the equipment make and serial number displayed on the main form.

Would I need to use a Select Case or perhaps incorporate the query that is
the record source for the main form? Am I biting off more than I can chew at
this time?

Thanks for any and all help!

Chris

 

[strive4peace]

Hi Chris,

are the Excel workbooks all in the same location or will the user need a
File Open dialog box to browse to a file?

"Would I need to use a Select Case"

No, it sounds like you can get the filename and sheetname from your data
-- just wondering about the path... and you should read this since you
will need to use VBA:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

we can help you with the code

Warm Regards,
Crystal


*
have an awesome day
*

 

[CB]

Hi Crystal,

There is only one file. I need to reference different sheets within the one
file depending on what is displayed for "Equipment" and "Serial Number" in
the current record (result of a query).

Thanks for the link. I'll check it out.

Chris

 

[strive4peace]

Hi Chris,

you're welcome

what is the file name and path?

what are a few of your sheet names?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CB]

Hi Crystal,

The filename is DMM_Verifications.xls and the path is
\\ISO\Inspection\Depot\Digital MultiMeter Verification Records.

Some sheet names include Jones_179, Smith_87_III, Hamil_87_III

Regards,
Chris

 

[strive4peace]

Hi Chris,

how do the sheet names correlate to your data?
ie:
1. EquipmentBrand_fieldname
2. _
3. SerialNumber_fieldname

if this is right, what are the respective fieldnames? If not, what is
the correct rule?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CB]

Hi again,

My database field names are per your "i.e." below; however, the Excel
worksheets are labelled differently. This was done by someone else to make it
easier to ensure the techs to open the appropriate sheet when they enter
their data. The general format for the sheet labelling is "TechSurname
EquipmentModel".

When I was first trying to figure this out, I was wondering if would be
helpful to add a field "WorksheetName" to the equipment table. Would this
help??

Chris

 

[strive4peace]

Hi Chris,

there is no reason to add a worksheet name if you can construct it from
your data...

here is the statement you will need:

Application.FollowHyperlink "C:\path\filename.xls", "SheetName!Address"

so, in code we can do this:

'~~~~~~~~~~~~~~~~
dim mFilename as string _
, mSheetname as string

mFilename = "\\ISO\Inspection\Depot\Digital MultiMeter Verification
Records\DMM_Verifications.xls"

mSheetname = "we need to work on this"

Application.FollowHyperlink _
mFilename , mSheetName & "!A1"
'~~~~~~~~~~~~~~~~

so, now we just have to write the code to calculate the sheetname by
referencing the controls that contain the following fields on your
form/subforms...

TechSurname
EquipmentModel

where are they located in relationship to where the code is going?

1. subform controlname
2. controlname


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CB]

Hi Crystal,

The name for my subform is simply “subfrmâ€. Creative, huh?! ïŠ

The name for the control for the equipment is “txtEquipmentâ€. This control
is a textbox found on both the main form (named “Enter Primary Equipment
Calibration Historyâ€) and the subform.

The name for the control for the techs’ surnames is “txtTSOfficerâ€. FWIW,
this control is on the sub-form and is a combo box that queries the
“Inspectors†table and returns just their initials (the table also contains
their full name).

Just so you’re clear, I’ll give an example…

Let’s say John Smith has a Fluke 87-III multimeter. In the “Inspectorsâ€
table, he is recorded by both “John Smith†and “JSâ€. He shows up in the
underlying records by his initials. The equipment is recorded in the records
as “Fluke 87-III Multimeter.†The spreadsheet that I need to launch would be
named “Smith 87 IIIâ€.

If these different iterations are problematic, it would be easy enough to
quickly modify either field names/data in the database or the names of the
sheets as I only have 15 records thus far.

Thanks so much for all your help! I really appreciate it.

Chris

 

[strive4peace]

Hi Chris,

you're welcome

try this:

'~~~~~~~~~~~~~~~~
dim mFilename as string _
, mSheetname as string

'make sure data that you need is filled out
if IsNull(me.subfrm.form.txtTSOfficer) then
msgbox "You have not specified a tech",,"Cannot open Excel"
exit sub
end if

if IsNull(me.txtEquipment) then
msgbox "You have not specified equipment",,"Cannot open Excel"
exit sub
end if

mFilename = "\\ISO\Inspection\Depot\" _
& "Digital MultiMeter Verification Records\" _
& "DMM_Verifications.xls"

mSheetname = me.subfrm.form.txtTSOfficer _
& "_" & me.txtEquipment

if MsgBox( _
"Sheetname is: " & mSheetname _
& vbCrLf & vbCrLf & "Click Yes to Open" _
, vbYesNo, "Open sheet?") _
= vbYes then

Application.FollowHyperlink _
mFilename , mSheetName & "!A1"

end if

'~~~~~~~~~~~~~~~~

not sure if I got the sheetname properly constructed... probably the
txtTSOfficer needs to get a column from the combo ... but this is a
start anyway

It would be better to use the initials in the sheetname in case you have
equipment that has a long number so the length of the sheetname doesn't
go over the limit

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CB]

Hi Crystal,

Thanks so much. I'll hopefully get to try this out today. And thanks for
reminding me about compiling the code. I've been away from Access for so long
(and even then hadn't done a lot of code) that I forgot about that.

Have a great weekend!

Chris

 

[CB]

Hi Crystal,

Well, I gave it a shot. Twice when I tried to compile Access hung and I just
about died! I'm not sure exactly what I did differently the third time but
things behaved that time.

Anyway, the dialogue box behaves properly (as in the message is okay and
corresponds to the worksheet names, and selecting 'Yes' opens the Excel file.
However, when Excel opens I get an error that "Reference is not valid."
Consequently, the file opens to the last worksheet I was on rather than the
referenced worksheet. I doubled checked the sheet names to what I had in the
field names and all looks okay. I added the underscore that you had in the
code but no difference. I tried commenting out the cell reference but that
didn't help either.

Chris

 

[strive4peace]

Hi Chris,

if the sheetname has a space in it, you need to put single quotes around it

mSheetname = "'sheetname with space'"

you can concatenate those in:

'~~~~~~~~~~~~
mSheetname = "'" & _
& me.subfrm.form.txtTSOfficer _
& "_" & me.txtEquipment _
& "'"
'~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[CB]

Hi Crystal,

Sorry for taking so long to reply. I don't know where you are but we had
long week-end here this passed week-end.

It worked like a charm! Thanks so much!

Chris

 

[strive4peace]

you're welcome, Chris happy to help

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*