Hi hope this is in he right forum!

[jpcurrie]

Hi hope this is in he right forum!


I have the following code on an onlick event in a subform which searches the selected record and displays it on the main form.....it works great in Access 2000 / Xp but it doesn't work in Access 2000 runtime.???

I originally thought it would be a references problem, but a quick check and they seem to be in order

they are:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data object 2.1
Micrsoft DAO 3.5

And the code is as follows`


Private Sub Form_Click()

Dim sq As String
sq = "" 'set variable

Dim rsClone As Recordset
Set rst = Me.RecordsetClone

Forms!Main!JobNumber.SetFocus

DoCmd.FindRecord Me!JobNumber, acEntire, False, acSearchAll, True, acAll

End Sub

What happen is that is sets focus to JobNumber ok but then it just stays on the same record?? *&%6*@@"!! instead of search and displaying hte correct one (what happens when not in runtime)

Any help would be great because this is the last hurdle "I think" in my struggle against my first access DB!


Cheers,


John

 

[oharab]

It's almost certainly a reference problem, kinda!!

You need to either tell Access which Database engine to use explicitly, or only give it one to choose from.
Either change
Dim rsClone As Recordset
to
Dim rsClone As DAO.Recordset

or remove the reference to Microsoft ActiveX Data object 2.1 or do both.

Also, try referencing the latest version of DAO-3.6 instead of 3.5. You may need to browse for it.

HTH

B ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[jpcurrie]

Hi oharab,

I've tried all of the above to no avail :-(.

Any more idea's ? in the mean time I'm going through technet putting patches on the test machine

BTW It works when I run access with /runtime on my dev machine (win xp) but fails on win 98 without access (ie runtime)

John

 

[cmmrfrds]

You seem to be using an older version of the MDAC library - Microsoft ActiveX Data object 2.1

It might be worthwhile to upgrade to a new version, such as 2.6 or above. There have been many improvements and some bug fixes since 2.1. You should be able to download it free from the Microsoft web site and install on the Win 98 PC. The MDAC libraries are built into the Window 2000 OS's, but not Win 98.

I don't know if this will fix the problem, but it would be good to upgrade anyway.

 

[jpcurrie]

Seems to be a win 98 specific problem,

--> Installed MDAC 2.6 + Jet Sp4 on target (Test) machine
still no luck ?????

What hte H*ll am I missing???

All works fine on Win2k and up but no 9x

Cheers


John

 

[cmmrfrds]

I agree with oharab that "It's almost certainly a reference problem, kinda!!"

Can you explain what your code is doing there may be another way to do it where the conflict will not show up.

The problem areas probably relate to the recordsetclone and the Macro findrecord. Can you explain how these are used since I am quite sure there are alternatives.

The basic problem is that Access 2000 supports 2 types of data objects DAO and ADO. I have only used the recordsetclone, which is a property of the Form, with DAO objects. Also, I am not sure if the findrecord macro works with ADO data objects. One has to stay within a library and if you use a method from the opposite library some type of problem will show up.

By using "Filter" and "Requery" on the subform you should be able to avoid the macro and the recordsetclone, but explain and we can determine an alternative.

 

[jpcurrie]

Hi, Back after a back holiday.

What the code is doing is..when the user clicks on the specified record in a subform which is based on a query which is based on the main table, the code then sets focus to JobNumber (which is an autonumber field) and searches for that record. This work fine in 2k/xp but not in 98.

Any other way I could code this would be greatly appreciated.

Originaly the code was sourced from this site and modified as this was my first real attempt at VB

Cheers,


John

 

[cmmrfrds]

Typically, a subform is the many part of a one-many relationship. A requery of the subform would be done when you move to a new record on the main form to display on the subform the records associated with the key value of the new record on the main form.

It appears you are doing something different.
When the user clicks on the record in the subform do you want to just display that record on the same subform and remove the rest of the records from the subform, or do bring up another subform with just the one record?

 

[jpcurrie]

Hi sorry for not being totally clear,

JobNumber is on the Main for [Main]

When a user clicks on the record on the subform (which is a cutdown record clone of Main)

the code baiscly changes focus to JobNumber on the main form and searches for the records clicked [using jobnumber]

Hope that is clearer

John

 

[cmmrfrds]

This is my understanding.
Main starts out with a bunch of job numbers showing all the detail on each job. This is where the user changes/adds information on an individual job.

Subform has the same job numbers but only a few fields so it looks like a list of job numbers. This functions as a list to quickly see all jobs.

By clicking on a job number in the subform, the main form and subform are redisplayed with information on only the 1 job. ?????????

 

[jpcurrie]

O.k here it goes...

The main form is bound to table jobook

It has 6 tab controls and various subforms bound to queries from jobbook and parcelcupboard (another table) all of which work fine. I then decided to implement a search form so what I did is created a blank tab control and moved it to the front of the tabs i.e the fist displayed tab.

On here I placed a unbound subform and 2 unbound texboxes and a search button

here is the code for the search button



Private Sub Command384_Click()
On Error GoTo Err_Command384_Click

Dim sq As String
sq = "" 'set variable

If Not IsNull(SearchClientRefBox) Then

sq = "WHERE FindClient.ClientReference LIKE ""*" & Me!SearchClientRefBox & "*"""
End If

If Not IsNull(SearchClientNameBox) Then

sq = "WHERE FindClient.Client LIKE ""*" & Me!SearchClientNameBox & "*"""
End If

If sq <> &quot;&quot; Then

sq = &quot;WHERE &quot; & Right$(sq, Len(sq) - 5) 'strip the leading WHERE
sq = &quot;SELECT * FROM FindClient &quot; & sq ' Find records in jobbook table & Where etc (variable)
Me.[FindClientsubform].Form.RecordSource = sq ' set record set based on results

End If

If sq = &quot;&quot; Then

MsgBox &quot;Please input a Client Reference or Whole / Part of a Client Name&quot;, vbExclamation, &quot;User Error!!!&quot;

End If

If Me.Form.RecordsetClone.RecordCount = 0 Then 'if no results (very unlikley since using like *?*)
Me.Form.RecordSource = &quot;SELECT * FROM JobBook WHERE False;&quot;
MsgBox &quot;No records match the criteria you entered.&quot;, vbExclamation, &quot;No Records Found&quot;
sq = &quot;SELECT * FROM FindClient&quot; 'Select All
Me.[FindClientsubform].Form.RecordSource = sq
End If

Exit_Command384_Click:
Exit Sub

Err_Command384_Click:
MsgBox Err.Description
Resume Exit_Command384_Click

End Sub


This search funtion displays the Fields:-

Name ClientRefence JobIn and Yearend from findclient (a query )which the recordset is select * from jobbook

The user is then presented with the search results in the subform, the appropriate client / year is found, the user clicks on the appropriate record in the subform, which them switches focus to Me!Jobnumber on the JobookPage tab control on the main form. Then searches this recordset for the jobnumber selected in the subform

This code works fine in 2k but not in 98!

(Subform on onclick event)

Private Sub Form_Click()


Dim rstClone As DAO.Recordset
Set rstClone = Forms![Main].RecordsetClone


Forms!Main!JobNumber.SetFocus
DoCmd.FindRecord Me!JobNumber, acEntire, False, acSearchAll, True, acAll

End Sub

Any ideas :-(

thanks in advance

John

 

[cmmrfrds]

Could it be a case of the filter on the main form not being cleared or set. After running the scenario on the Win 98 PC then go to the main form in design view and check what is in the filter. It should be your latest filter/where criteria.

 

[jpcurrie]

O.k, done that and it is all blank, both on 98 and 2k.

John