Button and rs.FindFirst

[alpapak]

Hi,

Where i am making a mistake???

Code:
Private Sub btn1_Click()
Dim dbCode As DAO.Database
Dim rstCode As DAO.Recordset
Set dbCode = CurrentDb
Set rstCode = dbCode.OpenRecordset("tblCode")

Dim strCheck As String

strCheck = Me.MainID.Value
With rstCode
.FindFirst "[MainID]=" & strCheck
End With

rstCode.Close
Set rstCode = Nothing
Set dbCode = Nothing

End Sub

When btn1 is click to findfirst in the tblCode.MainID

 

[vbaInet]

* What is the Data Type of MainID?
* Are you getting an error when you run the code? If you are, what is the error message?

 

[alpapak]

Forgot..

tblMain
MainID, AutoNumber
Main1, Number
Main2, Number

CodeID, AutoNumber
MainID, Number
Code1, Number
...
Code10, Number

frmMain
MainID
Main1
Main2

and bt1 as Button

All are numbers...

 

[vbaInet]

So I'll ask again -->

* Are you getting an error when you run the code? If you are, what is the error message?

 

[alpapak]

3251 error

Operation is not supported for this type of object.

 

[vbaInet]

Alright, you're doing this on a table. You need to use FindFirst on a query.

 

[alpapak]

Thxs....

Is it possible to check if MainID from frmMain exist in tblCode???
without a query...

 

[vbaInet]

You can use a DLookup() function... but it's better with a query.

 

[alpapak]

From my little vba...

code:
If DLookup("MainID", "tblCode", "MainID" = Me.MainID) = True Then
'do something
Else
'do something
End If

Is this ok... i test it and it always go to Else...
If tblcode.mainID is the same with frmMainID = True then do something

 

[vbaInet]

Almost. Have a look here:

http://access.mvps.org/access/general/gen0018.htm

You will also need the Nz() function as well because DLookup() can return Null.

 

[alpapak]

test it but always jump to else

code:
If DLookup("MainID", "tblCode", "MainID = " & Me.MainID) = True Then

 

[vbaInet]

DLookup() returns the corresponding value, it doesn't return True. You should just use DCount() instead and test if it's greater than 0.

 

[alpapak]

i found that :

If DLookup("MainID", "tblCode", "MainID = " & Me.MainID) = Me.MainID

is working fine...

MainID is always a number and never null or 0... it is autonumber...

Is it going to work always right???

 

[vbaInet]

Use a DCount() it's much better and yes it will always work.

 

[alpapak]

Something else...

Using recordset (at the top of the page) how to make it point or focus or move ,in the tblCode where tblCode.MainID is Me.MainID???

 

[vbaInet]

I've already told you that you can't use FindFirst with a table set. FindFirst will move to the record.

 

[alpapak]

And how can i point at that row inside the tblCode where MainID is the same???
Without a query...

I need to point in that row and then add data to Code1, Code2,...

tblCode
CodeID
MainID
Code1
..
Code10

 

[vbaInet]

Maybe I should say it again for the third time, you can't. It's a table, you need a query. How difficult is it to create a query based on your table and use that?

 

[alpapak]

Made qtrCode

Code:
Dim dbCode As DAO.Database
Dim rstCode As DAO.Recordset
Dim I As Integer
Dim CodeX As String

Set dbCode = CurrentDb
Set rstCode = dbCode.OpenRecordset("qtrCode")

rstCode.FindFirst "MainID = " & Me.MainID
For I = 1 To 10
CodeX = "Code" & I
rstCode(CodeX).Value = "0" <=====Error 3020 Update or CancelUpdate Without AddNew or Edit
Next I
rstCode.Update

 

[alpapak]

Found it...

Added...

Code:
rstCode.FindFirst "MainID = " & Me.MainID
rstCode.Edit
For I = 1 To 10