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

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.

that's not really an option.  Isn't there any other way - using late binding or anything of that nature?

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

It is a continuous form

Use a Dlookup() as the controlsource of a calculated control and drop the sqlServer table from the query.

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

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

Select allOpen in new window

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] & "'")

I still get the circular reference error.  I am putting the text you sent me in the control source for the text field.

=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

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.

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

Select allOpen in new window

is CustomerID the name of the TextBox for which CustomerID is the ControlSource?

Yes.  I did not change the name of the text box.  So the database field is customerID and the text box name is CustomerID

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...

its should read
     single quoite, double quote & strArgument & double quote single quote

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....

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.

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...

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 ?

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

You sure did!  Whew - very valuable tool - thanks for sticking it out with me!

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