A Lesson in Updateable Recordsets Needed

B

BK

I have an adp in Access 2002 with an SQL Server backend. I have a view
on the server (called INVOICE_COST_REV) which calculates all the revenue
and costs on an invoice by invoice basis and also shows when it was paid
and a check number. I am trying to create a data entry screen so the
user can just enter the invoice and it pops up the job number, invoice
amount and then two fields so she can enter the check number and date.
In an afterupdate event of entering the invoice number it queries this
view. I build a SQL statement in code and set it as the recordset for
the form. It takes only those fields I need from the view. It comes up
as it should, but the problem lies in the fact that it says it is a
non-updateable recordset so she obviously can't enter the check number
and date. Is there something I'm missing? How can I make this an
updateable recordset so she can enter the needed info?

Here is the code:

Private Sub txtInvoiceSearch_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT INVOICE_NO, WO_JOB_ID, TOTAL_REV, PAID, CHECK_NO"
strSQL = strSQL & " FROM dbo.INVOICE_COST_REV"
strSQL = strSQL & " WHERE dbo.INVOICE_COST_REV.INVOICE_NO='" &
Me!txtInvoiceSearch & "'"

With Me
.RecordSource = strSQL
.DataEntry = False
!txtWO_JOB_ID.Visible = True
!txtTOTAL_REV.Visible = True
!txtPAID.Visible = True
!txtCHECK_NO.Visible = True
End With

End Sub

TIA,
Bill
 
V

Vadim Rapp

B> How can I make this an
B> updateable recordset so she can enter the needed info?

Most likely you need to include the table's primary key in the query. For
more details,
check out the article "Determining When Query Results Can Be Updated" in
Access Help.

Vadim
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top