[RESOLVED] a question of query

I have a tabel from access 2k with field:

NoNota (autonumber)
tanggal (date)
kodepelanggan (text)
namapelanggan (text)
subtotal (number)
pot (number)
total (number)

when I query msql = "insert...." it work fine and autonumber created,
at the same the msql = "select * from..." doesnot return an autonumber
on form textbox.
I use command call save1 in the button click event

run time error
"either BOF or EOF is true, or the current record has been deleted."
"requested operation require a current record"

any sugestion is very much appreciated
thanks in advance

VB Code:

Sub Save1()
Dim msql As String
 
msql = "insert into tbnotajual(tanggal)values('" & dptanggal & "')"
 
con_data.Execute (msql)
rs.Requery
 
msql = "select * from tbnotajual" & _
       "where nonota in (select max(NoNota) from tbnotajual)"
 
txtnota.Text = rs.Fields(Val("NoNota"))
con_data.Execute (msql)
rs.Requery
txtkodebarang.SetFocus
 
End Sub

try this

VB Code:

txtnota.Text = rs.Fields(Val("NoNota"))

VB Code:

if rs.eof=true then
rs.movefirst
end if
txtnota.Text = rs.Fields(Val("NoNota"))

I think that your problem is with the requery method.
try declaring another recordset

Dim rs1 as recordset

Set rs1=con_data.Execute(msql)
Set rs=rs1

Quote:

---

Originally Posted by danasegarane

try this

VB Code:

txtnota.Text = rs.Fields(Val("NoNota"))

VB Code:

if rs.eof=true then
rs.movefirst
end if
txtnota.Text = rs.Fields(Val("NoNota"))

---

thanks for replays

now the error line is:

rs.movfirst

VB Code:

if rs.eof=true then
rs.movefirst
end if
txtnota.Text = rs.Fields(Val("NoNota"))

VB Code:

Sub Save1()
Dim msql As String
Dim rsTmp As ADODB.Recordset
 
msql = "insert into tbnotajual(tanggal)values('" & dptanggal & "')"
con_data.Execute msql
 
msql = "select * from tbnotajual" & _
       "where nonota in (select max(NoNota) from tbnotajual)"
Set rsTmp = con_data.Execute(msql)
If Not (rsTmp Is Nothing) Then 
   If (Not rsTmp.BOF) And (Not rsTmp.EOF) Then 
      txtnota.Text = rsTmp.Fields("NoNota").Value
   End If
   rsTmp.Close  
End If
'rs.Requery  'why are you requerying this recordset? what is its query?
txtkodebarang.SetFocus
 
End Sub

really help
much appreciated
thanks