ACCESS FEED: RE: [MS_AccessPros] Problem referencing values in query

Minggu, 25 November 2012

RE: [MS_AccessPros] Problem referencing values in query

But the code you posted didn't even reference a field named Location. As suggested, did you look at the caption properties? Did you check the actual field names?

Duane Hookom MVP
MS Access

> From: desertscroller@cox.net
>
> Hi Duane, I tried removing each field and found that any field that started with the word "LOCATION" fails. So I changed "LOCATION" to "LOC" and everything worked fine. "LOCATION" must be a reserved word. The table used in the query is a link excel file. I will need to run a macro in Excel to change all fields with "LOCATION" to "LOC". Thanks for your help. This project is becoming very involved and a real learning experience.
> Thanks again.
> Rod
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
> >

> > What happens when you remove one or the other field from the MsgBox? We also can't see the SQL of your query.
> >
> > Duane Hookom MVP
> > MS Access MVP
> >
> > > From: desertscroller@...
> > > Date: Mon, 26 Nov 2012 00:08:04 +0000
> > > Subject: [MS_AccessPros] Problem referencing values in query
> > >
> > > Hi all,
> > > I am having a problem retrieving values from a query. I am using Access 2010 on Windows 7. The following is the code where the issue exist. The issue is the MsgBox in the DO WHILE loop. After the record set is open ONE record exist. However, when trying to display a couple of the values an error is generated indicating the information does not exist. "Error 3265, Item not found in this collection." When the query is run standalone the field names appear with the appropriate data.
> > > Any suggestions, I will continue to research the issue. Thank you for your time.
> > > Rod
> > > ------------------------------------
> > > Private Sub Process_XL_Records()
> > > Dim rst As DAO.Recordset
> > > Dim tmpStr As String
> > >
> > > 'Set some local parameters for queries
> > >
> > > Set rst = CurrentDb.OpenRecordset("New_License_Without_Matching_tblTaxpayer")
> > > MsgBox "Record count = " & rst.RecordCount
> > >
> > > rst.MoveFirst
> > > Do While Not rst.EOF
> > >
> > > MsgBox rst![LICENSE DT] & " - " & rst![LOCATION STREET]
> > > tmpStr = ""
> > >
> > > rst.MoveNext
> > > Loop
> > > rst.Close
> > > Set rst = Nothing 'Deassign all objects.
> > > End Sub

[Non-text portions of this message have been removed]