mizveggie
asked on
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.
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.
ASKER
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
davlyo
ASKER
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
ASKER
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_s lcdpm],[Cu stomerID]= [dbo_slcdp m].[fcustn o])
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!
=DLookUp([fcompany],[dbo_s
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
If customerid is a number then ...
=DLookUp("[fcompany]","[db o_slcdpm]" ,"[Custome rID]=" & [dbo_slcdpm].[fcustno])
If customerid is text then ...
=DLookUp("[fcompany]","[db o_slcdpm]" ,"[Custome rID]='" & [dbo_slcdpm].[fcustno] & "'")
=DLookUp("[fcompany]","[db
If customerid is text then ...
=DLookUp("[fcompany]","[db
ASKER
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]","[db o_slcdpm]" ,"[Custome rID]='" & [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
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("S
Set rs = CurrentDb.OpenRecordset("S
--Set rs = CurrentDb.OpenRecordset("S
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].[
as peter57r said... if the Customer ID is text -use the first Set, if the Customer ID is a number -use the second.
ASKER
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 ].[Custome rID])
I have attached the code in the function (CustomerID is a string)
Thanks for all your help
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
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
is CustomerID the name of the TextBox for which CustomerID is the ControlSource?
ASKER
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
single quoite, double quote & strArgument & double quote single quote
ASKER
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.
ASKER
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 ?
how big is the MDB can you send it to me or send me a (small) example ?
ASKER
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].[Conta cts].[Cust omerID])
I was able to select GetCustName from the available functions
=GetCustName([Form].[Conta
I was able to select GetCustName from the available functions
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
You can lock the customer name field on the form sdo that it can't be changed.