• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

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

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
mizveggie
Asked:
mizveggie
  • 10
  • 10
  • 4
1 Solution
 
peter57rCommented:
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
 
mizveggieAuthor Commented:
that's not really an option.  Isn't there any other way - using late binding or anything of that nature?
0
 
dmlyo150Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mizveggieAuthor Commented:
It is a continuous form
0
 
peter57rCommented:
Use a Dlookup() as the controlsource of a calculated control and drop the sqlServer table from the query.
0
 
dmlyo150Commented:
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
 
mizveggieAuthor Commented:
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
 
peter57rCommented:
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
 
mizveggieAuthor Commented:
I still get the circular reference error.  I am putting the text you sent me in the control source for the text field.
0
 
peter57rCommented:
=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
 
dmlyo150Commented:

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
 
mizveggieAuthor Commented:
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
 
dmlyo150Commented:
is CustomerID the name of the TextBox for which CustomerID is the ControlSource?
0
 
mizveggieAuthor Commented:
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
 
dmlyo150Commented:
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
 
dmlyo150Commented:
its should read
     single quoite, double quote & strArgument & double quote single quote
0
 
mizveggieAuthor Commented:
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
 
dmlyo150Commented:

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
 
mizveggieAuthor Commented:
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
 
dmlyo150Commented:
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
 
mizveggieAuthor Commented:
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
 
dmlyo150Commented:
Got it!
=GetCustName([CustomerID])
0
 
mizveggieAuthor Commented:
You sure did!  Whew - very valuable tool - thanks for sticking it out with me!
0
 
dmlyo150Commented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 10
  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now