[MS_AccessPros] Re: Recordset is not updatable

 

Here is the SQL

strSQLSelect = " SELECT Buyers.Company, Buyers.Buyer_NUM, Buyers.BUYER_NAME, " & _
" Buyers.Buyer_Association, Buyers.Buyer_Status, Buyers.Buyer_WIP_Status, " & _
" Buyers.Buyer_Last_Upd, Buyers.Buyer_Last_Upd_By " & _
" FROM Buyers INNER JOIN Login_ID_Table ON (Buyers.CORP = Login_ID_Table.LG_Corp) and (Buyers.BUYER_NUM = Login_ID_Table.[LG_Buyer_Num]) " & _
" WHERE (((Buyers.Buyer_Status)= " & "'" & strActive & "'" & ") " & _
" And ((Login_ID_Table.LG_Login_ID)= " & "'" & strFilter & "'" & "))" & _
" ; "

I am interesting in hearing your comment on my attempt to split this query into 2 - first by getting the Login_ID_Table.[LG_Buyer_Num] from the Login_ID_Table using the information available in strFilter follow by another query to return the row which matches the where clause in the 2nd query.

I have tried using ONE and TWO recordset. The form displays nothing. Here is the code for the ONE recordset attemp.

set db = Currentdb()
strSQLSelect = "SELECT Login_ID_Table.LG_Buyer_Num, Login_ID_Table.LG_Corp " & _
" FROM Login_ID_Table " & _
" WHERE " & _
" ((Login_ID_Table.LG_Login_ID)= " & "'" & strFilter & "'" & ")" & _
" ; "


Set rs = db.OpenRecordset(strSQLSelect)

strFilter2 = rs!LG_Buyer_Num
strFilter3 = rs!LG_Corp

strSQLSelect = " SELECT Buyers.CORP, Buyers.BUYER_NUM, Buyers.BUYER_NAME, " & _
" Buyers.Buyer_Association, Buyers.Buyer_Status, Buyers.Buyer_WIP_Status, " & _
" Buyers.Buyer_Last_Upd, Buyers.Buyer_Last_Upd_By " & _
" FROM Buyers " & _
" WHERE (((Buyers.Buyer_Status)= " & "'" & strActive & "'" & ") " & _
" And (Buyers.CORP = " & "'" & strFilter3 & "'" & ") " & _
" And (Buyers.BUYER_NUM = " & "'" & strFilter2 & "'" & ")) " & _
" ; "

rs.Close

Display_The_Results:

Set rs = db.OpenRecordset(strSQLSelect)


If (rs.EOF Or rs.BOF) Then
rs.Close
Set dbs = Nothing
strMsg = "No Buyer information found for " & strFilter1

If MsgBox(strMsg, vbOKOnly) = vbOK Then
Cancel = -1
End If
GoTo Exit_Form_Open

End If


Form.RecordSource = strSQLSelect

rs.Close
Set rs = Nothing
Set db = Nothing

Thanks

Thomas

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Thomas-
>
>
>
> Why don't you post the SQL in a reply? We can help you see why it's not
> updateable and perhaps tell you how to fix it.
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Thomas
> Sent: Thursday, July 05, 2012 5:45 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Recordset is not updatable
>
>
>
>
>
> John,
>
> You are correct. The query joins 2 tables to retrieve the resultant rows. I
> guess I will have to code 2 separate queries to get the row I need to display.
>
> Thanks again for your very informative notes.
>
> Thomas
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> wrote:
> >
> > Thomas-
> >
> >
> >
> > In general, not updateable queries include:
> >
> >
> >
> > 1) Totals queries (GROUP BY, HAVING)
> >
> > 2) Queries that include a Totals query as a subquery
> >
> > 3) Any query using DISTINCT
> >
> > 4) Any query involving more than one table and one or more aren't JOINed
> >
> > 5) Any query that JOINs tables where the join key fields aren't unique it at
> > least one of the two tables
> >
> > 6) Any query joining tables that don't follow a "drill-down" pattern
> >
> > For example, A -> B -> C may be updatable whereas A -> B <- C is not
> >
> > 7) Any table using JOINs on calculated fields
> >
> >
> >
> > There are probably some other rules, but those are the worst offenders.
> >
> >
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > <http://www.viescas.com/> http://www.viescas.com/
> >
> > (Paris, France)
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Thomas
> > Sent: Thursday, July 05, 2012 3:48 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > Subject: Re: [MS_AccessPros] Recordset is not updatable
> >
> >
> >
> >
> >
> >
> > John,
> > That's interesting. I didn't realize there are restrictions on select
> statements
> > which cause the record set to be not updatable,
> >
> > where can I find more info on the restrictions?
> >
> > Thanks
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > wrote:
> > >
> > > Thomas-
> > >
> > >
> > >
> > > Curious why you're not just doing:
> > >
> > >
> > >
> > > Me.RecordSource = strSQLSelect
> > >
> > >
> > >
> > > What I want to see is what's in strSQLSelect - the SQL defined there must be
> > > resulting in a query that's not updateable.
> > >
> > >
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > <http://www.viescas.com/> http://www.viescas.com/
> > >
> > > (Paris, France)
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Thomas
> > > Sent: Thursday, July 05, 2012 7:45 AM
> > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > Subject: Re: [MS_AccessPros] Recordset is not updatable
> > >
> > >
> > >
> > >
> > >
> > >
> > > John,
> > > The Record source of the form is blank - nothing.
> > >
> > > The Event procedure for the 'On Open'
> > >
> > > Set db = currentdb()
> > > Set ts = db.Openrecordset(strSQLSelect)
> > > Me.Form.Recordsource = strSQLSelect
> > >
> > > Thomas
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> , John Viescas <JohnV@>
> > > wrote:
> > > >
> > > > Thomas-
> > > >
> > > >
> > > >
> > > > It would help to see the Record Source of the form (the SQL if a query)
> and
> > > the
> > > > code that's applying the filter.
> > > >
> > > >
> > > >
> > > > John Viescas, author
> > > > Microsoft Office Access 2010 Inside Out
> > > > Microsoft Office Access 2007 Inside Out
> > > > Building Microsoft Access Applications
> > > > Microsoft Office Access 2003 Inside Out
> > > > SQL Queries for Mere Mortals
> > > > <http://www.viescas.com/> http://www.viescas.com/
> > > >
> > > > (Paris, France)
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > From: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Thomas
> > > > Sent: Wednesday, July 04, 2012 6:45 AM
> > > > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com>
> > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > <mailto:MS_Access_Professionals%40yahoogroups.com>
> > > > Subject: [MS_AccessPros] Recordset is not updatable
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > The form shows this message when I attempt to update a field.
> > > >
> > > > In the 'Open' event, the VBA retrieves records based on criteria defined
> in
> > > the
> > > > 'where' clause.
> > > >
> > > > The form displays the record(s) retrieve. However, one of the field was
> > > defined
> > > > as a combo box which the user is able to select a value from the drop
> down.
> > > >
> > > > Any ideas how I can make it updatable?
> > > >
> > > > Thanks,
> > > > Thomas
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > >
> > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (9)

Recent Activity:

• New Members 5
• New Files 3

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___