Form linked to Linked Excel Table via ComboBox

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

Yesterday I linked my Access DB to an Excel Workbook, and specifically to a
single sheet named ‘Main’. I was able to control the creation of a dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am trying to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won’t do anything
(when I click in any of the ComboBoxes on the Form I can’t see any data at
all). I don’t know what changed between yesterday and today. What could
cause this? How can I reestablish my link between the Form and the Excel
sheet named ‘Main’?

Regards,
Ryan---
 
You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook, only the
link to it).
Create the link again and see if it works.

Jeanette Cunningham
 
Thanks Jeanette, but that's not it. I could swear that this thing was
working yesterday! It was late in the day, when I THINK I got it working,
and I was rushing to get my work done, but I swear it was working before I
downed my computer. Now, nothing works...



--
RyGuy


Jeanette Cunningham said:
You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook, only the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


ryguy7272 said:
Yesterday I linked my Access DB to an Excel Workbook, and specifically to
a
single sheet named 'Main'. I was able to control the creation of a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am trying to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do anything
(when I click in any of the ComboBoxes on the Form I can't see any data at
all). I don't know what changed between yesterday and today. What could
cause this? How can I reestablish my link between the Form and the Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
 
I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate queries.
If they do return records, then it looks like something with the form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




ryguy7272 said:
Thanks Jeanette, but that's not it. I could swear that this thing was
working yesterday! It was late in the day, when I THINK I got it working,
and I was rushing to get my work done, but I swear it was working before I
downed my computer. Now, nothing works...



--
RyGuy


Jeanette Cunningham said:
You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook, only the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


ryguy7272 said:
Yesterday I linked my Access DB to an Excel Workbook, and specifically
to
a
single sheet named 'Main'. I was able to control the creation of a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am trying to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & "
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do anything
(when I click in any of the ComboBoxes on the Form I can't see any data
at
all). I don't know what changed between yesterday and today. What
could
cause this? How can I reestablish my link between the Form and the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Hey Jeanette! Thanks for the follow up. I’ll look into those things
shortly. In the meantime, I made a discovery, of sorts. When I linked the
Form to the Excel ‘linked’ table, and I am NOT ON MY FIRM’S NETWORK, it works
fine. However, when I am doing this ON THE NETWORK, it does not work at
all!! For example, when the (Excel and Access) files are on my C-drive,
everything is fine. As soon as I load them on to the network, nothing works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have a
solution for this?

Regards,
Ryan---


--
RyGuy


Jeanette Cunningham said:
I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate queries.
If they do return records, then it looks like something with the form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




ryguy7272 said:
Thanks Jeanette, but that's not it. I could swear that this thing was
working yesterday! It was late in the day, when I THINK I got it working,
and I was rushing to get my work done, but I swear it was working before I
downed my computer. Now, nothing works...



--
RyGuy


Jeanette Cunningham said:
You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook, only the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


Yesterday I linked my Access DB to an Excel Workbook, and specifically
to
a
single sheet named 'Main'. I was able to control the creation of a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am trying to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & "
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do anything
(when I click in any of the ComboBoxes on the Form I can't see any data
at
all). I don't know what changed between yesterday and today. What
could
cause this? How can I reestablish my link between the Form and the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Ryan,
you might need to link to the network using UNC path instead of mapped drive
letters.
To use UNC path, when you are linking to the workbook, navigate to the
workbook using Network Neighbourhood instead of Expolorer.
Another possibility is that the permissions on the folder with the workbook
don't allow access to lock the workbook file, and this could stop access
from doing its stuff with the file.

Jeanette Cunningham



ryguy7272 said:
Hey Jeanette! Thanks for the follow up. I'll look into those things
shortly. In the meantime, I made a discovery, of sorts. When I linked
the
Form to the Excel 'linked' table, and I am NOT ON MY FIRM'S NETWORK, it
works
fine. However, when I am doing this ON THE NETWORK, it does not work at
all!! For example, when the (Excel and Access) files are on my C-drive,
everything is fine. As soon as I load them on to the network, nothing
works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have a
solution for this?

Regards,
Ryan---


--
RyGuy


Jeanette Cunningham said:
I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate
queries.
If they do return records, then it looks like something with the form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




ryguy7272 said:
Thanks Jeanette, but that's not it. I could swear that this thing was
working yesterday! It was late in the day, when I THINK I got it
working,
and I was rushing to get my work done, but I swear it was working
before I
downed my computer. Now, nothing works...



--
RyGuy


:

You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook, only
the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


Yesterday I linked my Access DB to an Excel Workbook, and
specifically
to
a
single sheet named 'Main'. I was able to control the creation of a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am trying
to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd &
"
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do
anything
(when I click in any of the ComboBoxes on the Form I can't see any
data
at
all). I don't know what changed between yesterday and today. What
could
cause this? How can I reestablish my link between the Form and the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the tip. I did a little research on the UNC and tried a few
things, but my two Forms still do not work. I may just go back to using
Excel. It takes less than 2 minutes to create a report in Excel and so far I
have spent a little over 2 weeks trying to do it in Access, but I still don't
have anything that is usable. I'm going to the bookstore today to look for
an advanced Access book. I have a few books, but none describe how to use
Access in an office environment. I find it hard to believe that Microsoft
would put Access out there for people to use but only if you are in a
non-network environment.

Thanks for the help Jeanette!!

Ryan--

--
RyGuy


Jeanette Cunningham said:
Ryan,
you might need to link to the network using UNC path instead of mapped drive
letters.
To use UNC path, when you are linking to the workbook, navigate to the
workbook using Network Neighbourhood instead of Expolorer.
Another possibility is that the permissions on the folder with the workbook
don't allow access to lock the workbook file, and this could stop access
from doing its stuff with the file.

Jeanette Cunningham



ryguy7272 said:
Hey Jeanette! Thanks for the follow up. I'll look into those things
shortly. In the meantime, I made a discovery, of sorts. When I linked
the
Form to the Excel 'linked' table, and I am NOT ON MY FIRM'S NETWORK, it
works
fine. However, when I am doing this ON THE NETWORK, it does not work at
all!! For example, when the (Excel and Access) files are on my C-drive,
everything is fine. As soon as I load them on to the network, nothing
works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have a
solution for this?

Regards,
Ryan---


--
RyGuy


Jeanette Cunningham said:
I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate
queries.
If they do return records, then it looks like something with the form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




Thanks Jeanette, but that's not it. I could swear that this thing was
working yesterday! It was late in the day, when I THINK I got it
working,
and I was rushing to get my work done, but I swear it was working
before I
downed my computer. Now, nothing works...



--
RyGuy


:

You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook, only
the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


Yesterday I linked my Access DB to an Excel Workbook, and
specifically
to
a
single sheet named 'Main'. I was able to control the creation of a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am trying
to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd &
"
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do
anything
(when I click in any of the ComboBoxes on the Form I can't see any
data
at
all). I don't know what changed between yesterday and today. What
could
cause this? How can I reestablish my link between the Form and the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Ryan,
I have used access with excel in a networked corporate environment with
great success.
I believe that a great many other people also have success with it as well.
Speak to your network IT people to see if they can help.


Jeanette Cunningham


ryguy7272 said:
Thanks for the tip. I did a little research on the UNC and tried a few
things, but my two Forms still do not work. I may just go back to using
Excel. It takes less than 2 minutes to create a report in Excel and so
far I
have spent a little over 2 weeks trying to do it in Access, but I still
don't
have anything that is usable. I'm going to the bookstore today to look
for
an advanced Access book. I have a few books, but none describe how to use
Access in an office environment. I find it hard to believe that Microsoft
would put Access out there for people to use but only if you are in a
non-network environment.

Thanks for the help Jeanette!!

Ryan--

--
RyGuy


Jeanette Cunningham said:
Ryan,
you might need to link to the network using UNC path instead of mapped
drive
letters.
To use UNC path, when you are linking to the workbook, navigate to the
workbook using Network Neighbourhood instead of Expolorer.
Another possibility is that the permissions on the folder with the
workbook
don't allow access to lock the workbook file, and this could stop access
from doing its stuff with the file.

Jeanette Cunningham



ryguy7272 said:
Hey Jeanette! Thanks for the follow up. I'll look into those things
shortly. In the meantime, I made a discovery, of sorts. When I linked
the
Form to the Excel 'linked' table, and I am NOT ON MY FIRM'S NETWORK, it
works
fine. However, when I am doing this ON THE NETWORK, it does not work
at
all!! For example, when the (Excel and Access) files are on my
C-drive,
everything is fine. As soon as I load them on to the network, nothing
works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have a
solution for this?

Regards,
Ryan---


--
RyGuy


:

I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate
queries.
If they do return records, then it looks like something with the form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




Thanks Jeanette, but that's not it. I could swear that this thing
was
working yesterday! It was late in the day, when I THINK I got it
working,
and I was rushing to get my work done, but I swear it was working
before I
downed my computer. Now, nothing works...



--
RyGuy


:

You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook,
only
the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


Yesterday I linked my Access DB to an Excel Workbook, and
specifically
to
a
single sheet named 'Main'. I was able to control the creation of
a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am
trying
to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd
&
"
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do
anything
(when I click in any of the ComboBoxes on the Form I can't see
any
data
at
all). I don't know what changed between yesterday and today.
What
could
cause this? How can I reestablish my link between the Form and
the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
The funniest thing of all is that I get data into the ListBoxes, I just can't
see it. If I click on the second row of data, or third row, or whatever, I
get the results I expect, but I have to 'know' what is on the second row,
because Access won't display it. Then, the invisible data is simply spit out
into the Report that I created, and once it is in the Report, it looks fine.
I may talk with the IT guy next week (he only comes in one time per week).
In the meantime, I hope to find an answer on my own.

Thanks for everything Jeanette!
Ryan--



--
RyGuy


Jeanette Cunningham said:
Ryan,
I have used access with excel in a networked corporate environment with
great success.
I believe that a great many other people also have success with it as well.
Speak to your network IT people to see if they can help.


Jeanette Cunningham


ryguy7272 said:
Thanks for the tip. I did a little research on the UNC and tried a few
things, but my two Forms still do not work. I may just go back to using
Excel. It takes less than 2 minutes to create a report in Excel and so
far I
have spent a little over 2 weeks trying to do it in Access, but I still
don't
have anything that is usable. I'm going to the bookstore today to look
for
an advanced Access book. I have a few books, but none describe how to use
Access in an office environment. I find it hard to believe that Microsoft
would put Access out there for people to use but only if you are in a
non-network environment.

Thanks for the help Jeanette!!

Ryan--

--
RyGuy


Jeanette Cunningham said:
Ryan,
you might need to link to the network using UNC path instead of mapped
drive
letters.
To use UNC path, when you are linking to the workbook, navigate to the
workbook using Network Neighbourhood instead of Expolorer.
Another possibility is that the permissions on the folder with the
workbook
don't allow access to lock the workbook file, and this could stop access
from doing its stuff with the file.

Jeanette Cunningham



Hey Jeanette! Thanks for the follow up. I'll look into those things
shortly. In the meantime, I made a discovery, of sorts. When I linked
the
Form to the Excel 'linked' table, and I am NOT ON MY FIRM'S NETWORK, it
works
fine. However, when I am doing this ON THE NETWORK, it does not work
at
all!! For example, when the (Excel and Access) files are on my
C-drive,
everything is fine. As soon as I load them on to the network, nothing
works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have a
solution for this?

Regards,
Ryan---


--
RyGuy


:

I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate
queries.
If they do return records, then it looks like something with the form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




Thanks Jeanette, but that's not it. I could swear that this thing
was
working yesterday! It was late in the day, when I THINK I got it
working,
and I was rushing to get my work done, but I swear it was working
before I
downed my computer. Now, nothing works...



--
RyGuy


:

You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook,
only
the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


Yesterday I linked my Access DB to an Excel Workbook, and
specifically
to
a
single sheet named 'Main'. I was able to control the creation of
a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am
trying
to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd
&
"
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do
anything
(when I click in any of the ComboBoxes on the Form I can't see
any
data
at
all). I don't know what changed between yesterday and today.
What
could
cause this? How can I reestablish my link between the Form and
the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Ryan,
there is a bug in SP3 released for Access. This affects combos just as you
describe.
There is a hotfix available to fix this bug.
Here are my notes:

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003 Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
If the field to which the combo box is bound has a Format property setting
for the field in the table, then yes, this is a bug that has been reported
by many ACCESS users during past weeks. It apparently is a bug in Office
2003 SP3, which was released by MS a short time ago. MS is aware of the
bug and is working on how to fix it.

In the meantime, the "complete workaround" is to remove the Format
property's value from the field's properties in the table's design view
Click to expand...


Jeanette Cunningham



ryguy7272 said:
The funniest thing of all is that I get data into the ListBoxes, I just
can't
see it. If I click on the second row of data, or third row, or whatever,
I
get the results I expect, but I have to 'know' what is on the second row,
because Access won't display it. Then, the invisible data is simply spit
out
into the Report that I created, and once it is in the Report, it looks
fine.
I may talk with the IT guy next week (he only comes in one time per week).
In the meantime, I hope to find an answer on my own.

Thanks for everything Jeanette!
Ryan--



--
RyGuy


Jeanette Cunningham said:
Ryan,
I have used access with excel in a networked corporate environment with
great success.
I believe that a great many other people also have success with it as
well.
Speak to your network IT people to see if they can help.


Jeanette Cunningham


ryguy7272 said:
Thanks for the tip. I did a little research on the UNC and tried a few
things, but my two Forms still do not work. I may just go back to
using
Excel. It takes less than 2 minutes to create a report in Excel and so
far I
have spent a little over 2 weeks trying to do it in Access, but I still
don't
have anything that is usable. I'm going to the bookstore today to look
for
an advanced Access book. I have a few books, but none describe how to
use
Access in an office environment. I find it hard to believe that
Microsoft
would put Access out there for people to use but only if you are in a
non-network environment.

Thanks for the help Jeanette!!

Ryan--

--
RyGuy


:

Ryan,
you might need to link to the network using UNC path instead of mapped
drive
letters.
To use UNC path, when you are linking to the workbook, navigate to the
workbook using Network Neighbourhood instead of Expolorer.
Another possibility is that the permissions on the folder with the
workbook
don't allow access to lock the workbook file, and this could stop
access
from doing its stuff with the file.

Jeanette Cunningham



Hey Jeanette! Thanks for the follow up. I'll look into those
things
shortly. In the meantime, I made a discovery, of sorts. When I
linked
the
Form to the Excel 'linked' table, and I am NOT ON MY FIRM'S NETWORK,
it
works
fine. However, when I am doing this ON THE NETWORK, it does not
work
at
all!! For example, when the (Excel and Access) files are on my
C-drive,
everything is fine. As soon as I load them on to the network,
nothing
works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have
a
solution for this?

Regards,
Ryan---


--
RyGuy


:

I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not
the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate
queries.
If they do return records, then it looks like something with the
form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




Thanks Jeanette, but that's not it. I could swear that this
thing
was
working yesterday! It was late in the day, when I THINK I got it
working,
and I was rushing to get my work done, but I swear it was working
before I
downed my computer. Now, nothing works...



--
RyGuy


:

You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook,
only
the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


message
Yesterday I linked my Access DB to an Excel Workbook, and
specifically
to
a
single sheet named 'Main'. I was able to control the creation
of
a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am
trying
to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " &
strProd
&
"
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do
anything
(when I click in any of the ComboBoxes on the Form I can't see
any
data
at
all). I don't know what changed between yesterday and today.
What
could
cause this? How can I reestablish my link between the Form
and
the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
The issue has been resolved. I am following up here/now for the benefit of
others who may encounter similar problems.

See this link for all details of resolution
http://www.microsoft.com/office/com...fc8321d563b&cat=&lang=en&cr=US&sloc=en-us&p=1

I could never get the ‘Linked Tables’ method working on the network. I
ended up creating a simple macro to do the import of the data. This link may
be helpful for importing techniques:
http://wagda.lib.washington.edu/gishelp/tutorial/AccessImport.pdf

If you do something similar, make sure you define the Data Type properly. I
encountered a small issue during the data import process. Some data in Excel
was formatted as ‘Number’ when in fact it should have been ‘Text’ or
‘General’. I changed the format type in Excel, saved the change, and ran the
import macro again, and it worked fine after that.

Thanks to Jeanette, and to Klatuu, for the generous help!!


Regards,
Ryan--


--
RyGuy


Jeanette Cunningham said:
Ryan,
there is a bug in SP3 released for Access. This affects combos just as you
describe.
There is a hotfix available to fix this bug.
Here are my notes:

"Combo box controls and list box controls display no value or
incorrect values in Access 2003 after you install Office 2003 Service
Pack 3." And a few more problems SP3 introduced.

Description of the Access 2003 post-Service Pack 3 hotfix package:
December 18, 2007 - 945674

http://support.microsoft.com/kb/945674
If the field to which the combo box is bound has a Format property setting
for the field in the table, then yes, this is a bug that has been reported
by many ACCESS users during past weeks. It apparently is a bug in Office
2003 SP3, which was released by MS a short time ago. MS is aware of the
bug and is working on how to fix it.

In the meantime, the "complete workaround" is to remove the Format
property's value from the field's properties in the table's design view
Click to expand...


Jeanette Cunningham



ryguy7272 said:
The funniest thing of all is that I get data into the ListBoxes, I just
can't
see it. If I click on the second row of data, or third row, or whatever,
I
get the results I expect, but I have to 'know' what is on the second row,
because Access won't display it. Then, the invisible data is simply spit
out
into the Report that I created, and once it is in the Report, it looks
fine.
I may talk with the IT guy next week (he only comes in one time per week).
In the meantime, I hope to find an answer on my own.

Thanks for everything Jeanette!
Ryan--



--
RyGuy


Jeanette Cunningham said:
Ryan,
I have used access with excel in a networked corporate environment with
great success.
I believe that a great many other people also have success with it as
well.
Speak to your network IT people to see if they can help.


Jeanette Cunningham


Thanks for the tip. I did a little research on the UNC and tried a few
things, but my two Forms still do not work. I may just go back to
using
Excel. It takes less than 2 minutes to create a report in Excel and so
far I
have spent a little over 2 weeks trying to do it in Access, but I still
don't
have anything that is usable. I'm going to the bookstore today to look
for
an advanced Access book. I have a few books, but none describe how to
use
Access in an office environment. I find it hard to believe that
Microsoft
would put Access out there for people to use but only if you are in a
non-network environment.

Thanks for the help Jeanette!!

Ryan--

--
RyGuy


:

Ryan,
you might need to link to the network using UNC path instead of mapped
drive
letters.
To use UNC path, when you are linking to the workbook, navigate to the
workbook using Network Neighbourhood instead of Expolorer.
Another possibility is that the permissions on the folder with the
workbook
don't allow access to lock the workbook file, and this could stop
access
from doing its stuff with the file.

Jeanette Cunningham



Hey Jeanette! Thanks for the follow up. I'll look into those
things
shortly. In the meantime, I made a discovery, of sorts. When I
linked
the
Form to the Excel 'linked' table, and I am NOT ON MY FIRM'S NETWORK,
it
works
fine. However, when I am doing this ON THE NETWORK, it does not
work
at
all!! For example, when the (Excel and Access) files are on my
C-drive,
everything is fine. As soon as I load them on to the network,
nothing
works.
There must be some special code to get this to work in a network
environment, right. Is anyone here aware of this? Does anyone have
a
solution for this?

Regards,
Ryan---


--
RyGuy


:

I assume that your excel table is still linked?
Can you create the query
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker
as a separate query and does it return results?
If the above works OK, the link to the worksheet is probably not
the
problem.

Check that the form allows edits.
Check that the form's recordsource is updateable.
Try the queries that are the row source for the combos as separate
queries.
If they do return records, then it looks like something with the
form.
What happens if you create new comobs, do they show any data?


Jeanette Cunningham




Thanks Jeanette, but that's not it. I could swear that this
thing
was
working yesterday! It was late in the day, when I THINK I got it
working,
and I was rushing to get my work done, but I swear it was working
before I
downed my computer. Now, nothing works...



--
RyGuy


:

You can delete and re-create the link to the workbook.
Delete the linked table in access (doesn't delete the workbook,
only
the
link to it).
Create the link again and see if it works.

Jeanette Cunningham


message
Yesterday I linked my Access DB to an Excel Workbook, and
specifically
to
a
single sheet named 'Main'. I was able to control the creation
of
a
dynamic
Report through a Form. The Row Source references this:
SELECT DISTINCT Main.Broker FROM Main ORDER BY Main.Broker;

The appropriate columns do exist in the Excel sheet that I am
trying
to
reference. The code under the Form looks like this:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String

' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If

' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " &
strProd
&
"
AND
[Status] " & strStatus
' Apply the filter and switch it on

DoCmd.OpenReport "SelReport", acPreview, , strFilter

With Reports![SelReport]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![SelReport].FilterOn = False
End Sub

I know this thing was working yesterday, but today it won't do
anything
(when I click in any of the ComboBoxes on the Form I can't see
any
data
at
all). I don't know what changed between yesterday and today.
What
could
cause this? How can I reestablish my link between the Form
and
the
Excel
sheet named 'Main'?

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
Back
Top