Re: Form linked to Linked Excel Table via ComboBox

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


Jeanette Cunningham



"ryguy7272" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1913313F-5CCC-414D-9B1A-D291DF1118B9@xxxxxxxxxxxxxxxx
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" wrote:

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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4A81EB42-92D7-4151-8BB0-8E785ACB5049@xxxxxxxxxxxxxxxx
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" wrote:

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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1469E711-8571-4A24-86CC-E406F81C31F9@xxxxxxxxxxxxxxxx
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" wrote:

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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6643C629-64E1-4F10-A95E-2D568FCAEEF6@xxxxxxxxxxxxxxxx
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" wrote:

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" <ryguy7272@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:43DED918-53D9-4929-AB9D-F6931A967C7C@xxxxxxxxxxxxxxxx
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---



--
RyGuy














.

Relevant Pages

  • Re: Form linked to Linked Excel Table via ComboBox
    ... Some data in Excel ... "Jeanette Cunningham" wrote: ... Speak to your network IT people to see if they can help. ... ' Build criteria string for Product field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Form linked to Linked Excel Table via ComboBox
    ... "Jeanette Cunningham" wrote: ... I have used access with excel in a networked corporate environment with ... Speak to your network IT people to see if they can help. ... ' Build criteria string for Product field ...
    (microsoft.public.access.modulesdaovba)
  • Re: Reboot in output from "last":
    ... > be because at some point there was a bug in there, ... running, new network socket listeners, misbehaviour of normal commands, ... This is another reason to keep /usr for packages provided by the system ... with a proper firewall in place and minimal installation ...
    (comp.os.linux.security)
  • Re: Form linked to Linked Excel Table via ComboBox
    ... To use UNC path, when you are linking to the workbook, navigate to the ... workbook using Network Neighbourhood instead of Expolorer. ... ' Build criteria string for Product field ... ' Combine criteria strings into a WHERE clause for the filter ...
    (microsoft.public.access.modulesdaovba)
  • Re: Form linked to Linked Excel Table via ComboBox
    ... It takes less than 2 minutes to create a report in Excel and so far I ... workbook using Network Neighbourhood instead of Expolorer. ... ' Build criteria string for Product field ... ' Combine criteria strings into a WHERE clause for the filter ...
    (microsoft.public.access.modulesdaovba)