code help on form!

[pfunk]

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

 

[pfunk]

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]));

 

[PHV]

If you want an updatable query then remove the DISTINCT predicate.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pfunk]

no dice PHV...

 

[PHV]

Does both tables have a PrimaryKey ?
What about this ?

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

 

[pfunk]

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

 

[TheAceMan1]

How are ya pfunk . . .

[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!
Also FAQ181-2886 Worthy Reading!