Opening a related form with the current record 2

[pollenman]

I have a DB wich shows products whitih the form I would like to Click on the Supplier field of an existing record and open the Supplier form with the focus being on the Supplier record that was chosen, I can get the Supplier form to open but I am unable to get focus on the chosen Supplier. Do I need to create an Event or can it be done through a Macro and how would I acheive my Goal.

Thank you in advance

Pollenman

 

[chrissie1]

try it with some code (i hate macro's) in the onload event of the form supplier

and try this code

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & str(Nz(Me![Combo5], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

you will need to change the names [id] = the name of the field in the suppliers form. and replace with me![combo5] with

forms![productsformname].form![suppliercontrolname]

this is the name of the first form with thename of the control where you click on to get to this second form

easy isnt it "What a wonderfull world" - Louis armstrong

 

[pollenman]

Thanks chrissie1 for your answer. I tried the code however I believe I have read the respose wrong have look and see what u think.
Name in the suppliers form is "SupplierName"
The First Form control "ProductGroupDetails" "SupplierName"


Private Sub Form_Load()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SupplierName] = "& Str(Nz(Me![forms![ProductGroupDetails].form![SupplierName]]
, 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark





End Sub

 

[chrissie1]

does it give any errors or what does it do? "What a wonderfull world" - Louis armstrong

 

[pollenman]

Comes up with syntax error and the line below is in read.

rs.FindFirst "[SupplierName] = "& Str(Nz(Me![forms![ProductGroupDetails].form![SupplierName]

, 0))

 

[chrissie1]

rs.FindFirst "[SupplierName] = "& Str(Nz(Me![forms![ProductGroupDetails].form![SupplierName]

, 0))

find the difference

rs.FindFirst "[SupplierName] = "& Str(Nz(forms![ProductGroupDetails].form![SupplierName], 0)) "What a wonderfull world" - Louis armstrong

 

[pollenman]

Chrissie1

Ran code. it commes up with an error Type mismatch.

I know it is properply me :-(

The form with the field in that i want to click on is "ProductGroupDetails"
The name of the field I click on is "SupplierName"

The form I want to open is "SupplierData" with the field "SupplierName" matching the 2 forms.

I put the event in OnLoad in "SupplierData" is that correct?

Thank you for patience

 

[chrissie1]

perhaps its this you need

rs.FindFirst "[SupplierName] = '"& Str(Nz(forms![ProductGroupDetails].form![SupplierName], 0)) & "'"

its in the correct event "What a wonderfull world" - Louis armstrong

 

[pollenman]

Sorry Chrissie one, still coming up with error 13 type missmatch.

So close yet so far.:-(

 

[chrissie1]

is the suppliersname on the productgroupdetails a combobox?

"What a wonderfull world" - Louis armstrong

 

[pollenman]

It is not a Combobox it is a text box: field.

 

[chrissie1]

when you get the error does it go to the line (i hope i does)
if it does then select this piece of text and rigght click on it then select add watch and then tell me what the value is you can see in the bottom of your screen in the watch pane

forms![ProductGroupDetails].form![SupplierName]

and else try this first

rs.FindFirst "[SupplierName] = '"& forms![ProductGroupDetails].form![SupplierName] & "'"


if this works forget the above
"What a wonderfull world" - Louis armstrong

 

[pollenman]

Watch : : Forms![ProductGroupDetails].Form![SupplierName] : <Out of context> : Empty : Form_SupplierData.Form_Load

The second piece comes up with Invalid watch expression

 

[chrissie1]

i didnt mean to put the second statement in the watch just replace it with the previous rs.findfirst and try it in the code

for the watch. Are you sure that the form is open (productgroupdetails) at the same time as you execute the code
&quot;What a wonderfull world&quot; - Louis armstrong

 

[linda55]

I don't mean to interrupt, but could I suggest another solution. I created a command button on the productID form to open the supplierID form. The supplierID form is based on a query. In the supplierID of the query build a criteria [Forms]![frmProductIDName]![SupplierID]. If your ProductId form has more than one supplierID (as you might with a mainform and subform situation); then you can put [Enter SupplierID] in the criteria of the query. This requires the operator to enter the supplierID. Hope this helps.

 

[pollenman]

You are a star Chrissie1 it works I replaced the code with rs.Findfirst and it woks.

 

[chrissie1]

finally

&quot;What a wonderfull world&quot; - Louis armstrong