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

Senin, 26 November 2012

Re: [MS_AccessPros] Problem referencing values in query

I did check the caption properties which were blank. The code had the following line:
MsgBox rst![LICENSE DT] & " - " & rst![LOCATION STREET]
The field name [LOCATION STREET} was the problem in fact all fields with LOCATION as part of the name acted the same. When I rename LOCATION to LOC; i.e. [LOCATION STREET} to [LOC STREET], then every then worked great. I hope that makes sense. The only thing I can think of is LOCATION must be a reserved word.
Thanks for the help. This group has help me learn a lot and always points me the issues and solutions. Thanks again.

Rod

--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...> wrote:
>
> 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@...
> >
> > 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]
>