Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

code help on form!

Status
Not open for further replies.

pfunk

Technical User
May 7, 2007
37
GB
i have a form that is bound to qry in access 2003 everything was working file until i had to link another table by account_num and it says the records set us not update able???
 
here is the qry

SELECT DISTINCT [enter home phone] AS [Phone Search], *
FROM T_Phones INNER JOIN T_Collection_Records ON T_Phones.Account_Num = T_Collection_Records.Account_Num
WHERE ((([enter home phone])=[T_Phones]![Phone]));
 
If you want an updatable query then remove the DISTINCT predicate.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Does both tables have a PrimaryKey ?
What about this ?
SQL:
SELECT *
FROM T_Phones INNER JOIN T_Collection_Records ON T_Phones.Account_Num = T_Collection_Records.Account_Num
WHERE T_Phones.Phone=[enter home phone]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
both tables have a primary key. i was told i may need some code and i subed out some work last night and the guys code still dont work i am now getting a compile error "user-defined type not defined"

Private Sub Phone_Seeker_Click()
On Error GoTo Error_ME2
Dim aCCNT2 As String, phonesee As String, accnt56 As String
phonesee = InputBox("Please enter a phone number...", "PN CREDIT SERVICES V7.6", "000-000-0000")

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intResult As Integer
Dim strSQL As String
Dim mostLikely As String
Dim strSQL2 As String
Dim strWhere As String
Set db = CurrentDb
strSQL = "SELECT Count(T_Phones.Account_Num) AS FoneCount, T_Phones.Account_Num As soughtAccnt"
strSQL = strSQL & " FROM T_Phones WHERE (((T_Phones.Phone)='" & phonesee & "')) GROUP BY T_Phones.Account_Num ORDER BY Count(T_Phones.Account_Num) DESC;"
strSQL2 = ""
strWhere = ""
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount <> 0 Then
Do Until rs.EOF
intResult = rs("FoneCount")
mostLikely = rs("soughtAccnt")
strWhere = strWhere & "((Account_Num)='" & mostLikely & "') Or "
rs.MoveNext
Loop
rs.Close
db.Close
strWhere = Left(strWhere, Len(strWhere) - 4)
If (intResult = 1) Then
DoCmd.OpenForm "F_Q_T_Collection_Records_Phone_Search", , , "Account_Num = '" & mostLikely & "'"
ElseIf (intResult > 1) Then
MsgBox "Phone number found on multiple accounts!" & vbCrLf, vbOKOnly, "PN CREDIT SERVICES V7.6"
DoCmd.OpenForm "F_Q_T_Collection_Records_Phone_Search", , , , acFormEdit

strSQL2 = "SELECT * FROM T_Collection_Records "
strSQL2 = strSQL2 & "WHERE ("
strSQL2 = strSQL2 & strWhere
strSQL2 = strSQL2 & ");"
Forms!F_Q_T_Collection_Records_Phone_Search.RecordSource = strSQL2
Else
MsgBox "Phone number not found!" & vbCrLf, vbOKOnly, "PN CREDIT SERVICES V7.6"
End If
End If
Exit_Point:

Exit Sub

Error_ME2:
Resume Exit_Point
End Sub
 
How are ya pfunk . . .

pfunk said:
[blue] ... getting a compile error "user-defined type not defined".[/blue]

Something is [blue]missing a reference[/blue] (tools-References), could be DAO. You'll have to check ...

[blue]Your Thoughts? . . .[/blue]

See Ya . . .

Be sure to see FAQ219-2884 Worthy Reading! [thumbsup2]
Also FAQ181-2886 Worthy Reading! [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top