Open form from a combo

[anujkmehrotra]

Hi,
I am trying to open a form from a combo (txtSrch) as per ColourID but it replies an error 3070 and debugs to following bold line. Please guide what I missed:

Code:

Private Sub txtSrch_AfterUpdate()
Dim D As Database
Dim rsCust As Recordset
Dim Criteria As String
Set D = CurrentDb

Set rsCust = D.OpenRecordset("tblColour", DB_OPEN_DYNASET)
Criteria = "[ColourID] = " & [txtSrch]
rsCust.FindFirst Criteria

Me!ColourID = rsCust("ColourID")
Me!ColourName = rsCust("ColourName")
rsCust.Close
Me.ColourID.SetFocus
End Sub

Thanks
Anuj

 

[vbaInet]

And what is the error message?

This is what you should be doing:

http://baldyweb.com/wherecondition.htm

 

[anujkmehrotra]

Run-time error '3070'

The Microsoft Jet engine does not recognize 'BLk' as a valid field name or expression.

where 'BlK' = selected ColourId from combo

 

[vbaInet]

Use the method explained in the link provided.

 

[anujkmehrotra]

I'm not opening second form whereas opening the same form.

 

[vbaInet]

So what is the point of opening the same form? If you want to filter the currently opened there are several ways to achieve this. The easiest is to use the Filter method of the form.

 

[anujkmehrotra]

I'm using an unbound form (without Record Source or Control Source) and also unbound fields (ColourId & ColourName).

So, I trying to edit the specific record on the same form through combo.

Please guide..

Thanks,
Anuj

 

[vbaInet]

Ok, first of all you cannot use FindFirst with a table. It has to be a query.
Second, what's your reason of using an unbound form?

 

[anujkmehrotra]

Hi vbaInet,

I'm trying to upgrade my old project into the most secured and error less concept. So, I want user to work more easily and secured like any other applications (VB, Oracle & JAVA) where user has full control over entry, view and report as well as less chances of mistakes.
But in traditional concept of MS Access, user can do some silly mistakes like extra records created, any many other.

But at the same time I found the found the problem in the following line:

Criteria = "[ColourID] = " & [txtSrch]

So, I changed it into this and succeeded:

Criteria = "[ColourID] = " & Chr(34) & [txtSrch] & Chr(34)

Please advice me whether should I upgrade my project or not?

Thanks,
Anuj

 

[vbaInet]

Good job Anuj.

Please advice me whether should I upgrade my project or not?

Thanks,
Anuj

Unfortunately, I'm not in a position to advise you whether or not to "upgrade". You will need to create a new thread giving us your business case, business logic, data requirements and data flow and someone will be able to advise.