Record Search via Combo Box-- wierd error

[RLHartness]

I've used Access for a little while on small projects here and there and I am just beginning to get the hang of Accesses in house VBA.

On a form I have a combo box at the top of my form that searches a table and then should output the data. The code for it is:



On Error GoTo ErrorHandler

Dim strSearch As String

strSearch = "[cltSSN] = " & Chr$(39) & Me![Combo160] & Chr$(39)

Me.Requery
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit


Whenever the form is loaded no record is loaded. When I try to execute the search (either by typing in a name, or selecting a record from the drop down box) I receive the following error:

Error No: 3021; Description: No current record.

I would assume that there was an error with my code but there is one little problem. Whenever the form is loaded in form view, I switch to design view and then back to form view, the fiirst record is loaded and the search box works perfectly. I have been really confused with this and have not found a solution.

I would also like to point out that upon loading the form I have tried to immeadiately load a record, but this does not work and the I am not receiving any error messages.

If you can help please do.


Possible needed extra info:


Form load code that I tried:


Private Sub Form_Load()

DoCmd.GoToRecord , , acFirst

End Sub


On the form there is another combo box. It has a default value. I have no clue if this could be part of the problem, but I thought I would state it just in case.

Need any more information, please ask.

 

[Fizzio]

Are you triggering the search via the after update of the combo?

Also a way around this is allocate the recordset to a variable eg

dim myrs as dao.recordset
set myrs = me.recordsetclone
myrs.movelast
myrs.movelast

On Error GoTo ErrorHandler

Dim strSearch As String

strSearch = "[cltSSN] = " & Chr$(39) & Me![Combo160] & Chr$(39)

Me.Requery
myrs.FindFirst strSearch
Me.Bookmark = myrs.Bookmark

etc...

 

[RLHartness]

I forgot to put that bit of info. This is within the After Update portion of the code. I tried using your code (in the After update area) and it's not working.

Thanks, but I think I might have found something that could help.

The nav buttons don't work either until I follow the same proceedure of going into design view and then back to form view.

I think that for some reason, the form is not loading the table into a record set until then. I can search for it in the unbound combo box, but it won't retrieve the data until I follow the above proceedure.

My record source for the form is simply the name of the table it is pulling that data from. I have tried to represent the table in different ways, and as I expected that didn't help.

 

[Fizzio]

2 things.

Why are you using the me.requery statement in your code if you are taking the info directly from a table and not from a dynaset?

you could also try this,

remove the recordsource from the form, save it, open it in design mode then replace the form's recordsource with the original table, save it again.

I have your code working without a hitch in a test form. Is there some other underlying code in the form?

 

[RLHartness]

I do not have any additional code within the form except for the generic code that is written by the button wizard.

I'm using the me.requery statement because I got the code from a book. I don't believe their is a problem with the combo box code. Because the same problems are occuring with the Navigation buttons at the bottom of the form. For all my nav tools to work I have to go into design view and then back into form view. Everything works perfectly then.

I think that some how the problem lies within the intial loading of the form, since no record is loaded. I tried what you suggested and I set my record source for the form to:

tblClient

the table I am pulling my data from. Is there an alternate way that I can load a table for a form that I might be over looking?

 

[Fizzio]

Have you tried rebuilding your form. I know it sounds a bit drastic but it can often solve these mysterious problems!

 

[RLHartness]

Noooooo... Anything but that! Yeah I might have to try that, although I'm a little apathetic b/c I have two forms with the same problem and they are very large.

I guess I can try the old cut and paste of 95% of it though. I'll post if it helps, if anyone else out there can figure this out, please do.

 

[Fizzio]

I know - swear words and heartsinking idea, sorry

 

[Paul_B]

I've just had the same error with a combo box on my form and traced the problem back to the actual field in my table that the combo was linked to.

I deleted the field and saved the table etc then recreated the field and now the combo box works and the form populates!

(and I still don't know what was wrong with the field)

Good Luck

Paul.