Solved

Recordset is not updateable - One table Access 2007, other table SQL

Posted on 2009-05-14
24
287 Views
Last Modified: 2012-05-07
I want a simple form where users can edit contact information.  All of the information, except customer name, is stored in an access database that is linked to this front end.  However, the customer name is on our SQL server and is joined into access via a linked table that is not updateable from Access.  I do not want the user to be able to change the customer name, but I need it there for display purposes.  The record source for the form is simply:
SELECT Contact.*, dbo_slcdpm.fcompany
FROM Contact INNER JOIN dbo_slcdpm ON Contact.CustomerID = dbo_slcdpm.fcustno;

Any advice on how to make this work would be greatly appreciated.
0
Comment
Question by:mizveggie
  • 10
  • 10
  • 4
24 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 24388072
I think you will have to make the slcdpm table updateable.
You can lock the customer name field on the form sdo that it can't be changed.
0
 

Author Comment

by:mizveggie
ID: 24388336
that's not really an option.  Isn't there any other way - using late binding or anything of that nature?
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24388876
is the Form's Default View -Single Form, Continuous Form or other?  ...if it is Single Form, how do you get the next Customer? Click a button? Select a ComboBox?
davlyo
0
 

Author Comment

by:mizveggie
ID: 24388967
It is a continuous form
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24390131
Use a Dlookup() as the controlsource of a calculated control and drop the sqlServer table from the query.
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24396249
Write a VBA Function with one argument (CustomerID) and Query the SQL Server table. Use the Function in the ControlSource of the TextBox in which you want the Customer Name displayed. If you need assistance with writing a Function; using a function to return the value of a Select statement; or anything else. Let me know -I will go more in depth. -davlyo
0
 

Author Comment

by:mizveggie
ID: 24396671
I am interested in the DLookup option, but I cannot make it work.  I make the control source of the text box:
=DLookUp([fcompany],[dbo_slcdpm],[CustomerID]=[dbo_slcdpm].[fcustno])
however I get a circular reference error in the formula.  

I am very interested in the VBA function solution as well, but I need more help to implement.  I have attached the code I wrote in the function, but I am not sure it is the most effective or efficient.  Then, I don;t know how to set the control source for the text box to be the result of the function.  Thanks for your help!
Private Function CustName(strCustID) As String

Dim rs As DAO.Recordset
 

Set rs = CurrentDb.OpenRecordset("Select fcompany from dbo_slcdpm where fcustno = '" & Me.CustomerID & "'")

If Not (rs.BOF And rs.EOF) Then

CustName = rs.Fields(0)

End If

Open in new window

0
 
LVL 77

Expert Comment

by:peter57r
ID: 24397001
If customerid is a number then ...
=DLookUp("[fcompany]","[dbo_slcdpm]","[CustomerID]=" & [dbo_slcdpm].[fcustno])

If customerid is text then ...
=DLookUp("[fcompany]","[dbo_slcdpm]","[CustomerID]='" & [dbo_slcdpm].[fcustno] & "'")
0
 

Author Comment

by:mizveggie
ID: 24397034
I still get the circular reference error.  I am putting the text you sent me in the control source for the text field.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 24397159
=DLookUp("[fcompany]","[dbo_slcdpm]","[CustomerID]='" & [dbo_slcdpm].[fcustno] & "'")
Fcompany should be the company name you want as the result.
dbo_slcdpm should be the table you are looking the value of fcompany in.
Customerid should be the field in dbo_slcdpm that matches the value in your form.
[dbo_slcdpm].[fcustno] is , I can see now, incorrect and  should be the name of the textbox on your form that contains the value you want to match against Customerid
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24397170

Private Function CustName(strCustID) As String
Dim rs As DAO.Recordset
 
--Set rs = CurrentDb.OpenRecordset("Select fcompany from dbo_slcdpm where fcustno = '" & Me.CustomerID & "'")
Set rs = CurrentDb.OpenRecordset("Select fcompany from dbo_slcdpm where fcustno = '" & strCustID & "'")
--Set rs = CurrentDb.OpenRecordset("Select fcompany from dbo_slcdpm where fcustno = " & strCustID & ")
If Not (rs.BOF And rs.EOF) Then
CustName = rs.Fields(0)
End If
then call the funtion from the control source of the text box
=CustName([Form].[frmNm].[CustomerID])
as peter57r said... if the Customer ID is text -use the first Set, if the Customer ID is a number -use the second.
0
 

Author Comment

by:mizveggie
ID: 24397517
I do not understand the exact syntax I should use for the Dlookup function - do you think you could spell it out for me?

I tried the Function solution, but on the form in the customer name field I get #NAME? for all records
This is the Control Source for the text box:
=CustName([Form].[Contacts].[CustomerID])

I have attached the code in the function (CustomerID is a string)

Thanks for all your help
Private Function CustName(strCustID) As String

Dim rs As DAO.Recordset
 

Set rs = CurrentDb.OpenRecordset("Select fcompany from dbo_slcdpm where fcustno = '" & strCustID & "'")

If Not (rs.BOF And rs.EOF) Then

CustName = rs.Fields(0)

End If
 
 

End Function

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 2

Expert Comment

by:dmlyo150
ID: 24398197
is CustomerID the name of the TextBox for which CustomerID is the ControlSource?
0
 

Author Comment

by:mizveggie
ID: 24411370
Yes.  I did not change the name of the text box.  So the database field is customerID and the text box name is CustomerID
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24411406
check out the number of closing single and douple quotes you have in your select argument. looks like you have one too many douple quotes at the end...
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24411422
its should read
     single quoite, double quote & strArgument & double quote single quote
0
 

Author Comment

by:mizveggie
ID: 24411434
right, except the final double quote closes the quote originating before the word select.  I've never gotten the select statement to work without enclosing the entire string in quotes....
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24411566

got it -srry-humor me. put the Select statement in a variable and remove the error catch
DIM st as stringLet st =  "Select fcompany from dbo_slcdpm where fcustno = '" & strCustID & "'"
Set rs = CurrentDb.OpenRecordset(st)
Remove the If not error catch
If you think you need the error catchTry&
Let castname = nz(rs(0),)
Otherwise just try&
Let custname = rs(0)
Question: did you write the Select statement yourself or did you copy it fro ma query? It looks correct, but I cant know for sure.
0
 

Author Comment

by:mizveggie
ID: 24412786
I am still getting the #Name? error.  I think this is the same problem as the DLookup function with a circular reference.  I do not understand what is circular about the reference, unless it has to do with the fact that this is a continuous form with multiple records...
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24413211
are you sure the CustID is a String rather than a number? (question based upon my own customer numbers)
how big is the MDB can you send it to me or send me a (small) example ?
0
 

Author Comment

by:mizveggie
ID: 24414453
I did some more troubleshooting and it appears that the function is never being called.  I put a break point at the beginning of the function and it is never activated.  I made the function global and put the following code in the control source for the text box:
=GetCustName([Form].[Contacts].[CustomerID])

I was able to select GetCustName from the available functions

0
 
LVL 2

Accepted Solution

by:
dmlyo150 earned 500 total points
ID: 24414716
Got it!
=GetCustName([CustomerID])
0
 

Author Comment

by:mizveggie
ID: 24414841
You sure did!  Whew - very valuable tool - thanks for sticking it out with me!
0
 
LVL 2

Expert Comment

by:dmlyo150
ID: 24414967
sorry it took so long -haven't touched access in a year or more -worked with it for 10+ years prior -good luck!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now