Use popup form to select a record on main form

[RexesOperator]

I'm not sure how to search on this, even in the advanced search. If this has been answered, could you point me in the right direction?

I have a main form AddNewCompany (the infamous tab control that now works - thanks to your combined efforts). On it I have a command button that pops up a form of continuous forms with all the companies in the table (the query calls the CompanyID and CompanyName fields only). I have attached an image of the interface.

This means the user can see if the company already exists and doesn't enter it again. (I'm sure there are more effecient ways of doing this, but this is simple and it works. I also know key fields should be autonumbers instead of text, but I have reasons for doing it this way).

What I would like to be able to do is click on the CompanyID field and have the companies information show up in the main form.

 

[llkhoutx]

Use the ID you get in the popup to set a bookmark in a recordsetclone of for calling form, then set the form bookmark to the recordetclone bookmark. It's not rocket science.

Search this sitefor recordset clones and bookmarks. I think I've previously posted this code.

 

[RexesOperator]

It's not rocket science if you happen to be a rocket scientist. But, if you're a plumber ..

After doing a bit more searching on the keywords you suggested, I found this code. Unfortunately I forgot to copy its originator. I am posting it for others' benefit:

Normally the best way to jump to a specified record matching a criteria, you set the form's bookmark property.

I know you say that your VB isn't too good, but it's never too late to learn.

in the onclick event of your command button, use this code

Dim rst as recordset
Set rst=forms![mainformname].recordsetclone
rst.findfirst "[companynamefield]='" & me![txtcompanyname] & "'"
if rst.nomatch=false then
forms![mainformname].bookmark=rst.bookmark
endif

Change the [mainformname] to your main form's name, change [companyfieldname] to the name of the field in your table that stores the company name, change [txtcompanyname] to the name of the textbox on the popup form that displays the company name.

Basically what this code does is creates an exact replica (clone) of the main forms records. Then it looks through all of those records until it finds the company displayed on the popup form. If it finds the company, it sets the bookmark property of the main form to the clone's bookmark. In effect moving to the appropriate record.

 

[llkhoutx]

Teach a man to fish . . .

I'm glad that you found your solution.

 

[RexesOperator]

On the other hand (to carry on with the water theme) you need to know where the lake is!

 

[llkhoutx]

You ask where it was and I told you.

 

[Niniel]

What version of Access was that for?
In Access 2002 this generates all kinds of errors.
I've changed a few things with the help of the wizard, but there doesn't seem to be anything that can replace the rst.nomatch expression - I get a "Compile error - method or data member not found"

Dim rst As Recordset
Set rst = Forms![frmBrowseApplications].RecordsetClone
rst.Find "[CIK]= Me.CIKSearch"
If rst.nomatch = False Then
Forms![frmBrowseApplications].Bookmark = rst.Bookmark
End If

 

[llkhoutx]

You have to use

rst.Find "[CIK]=" &  Me.CIKSearch

for CIKSearch being an integer;

rst.Find "[CIK]='" &  Me.CIKSearch & "'"

for CIKSearch being text; and

rst.Find "[CIK]=#" &  Me.CIKSearch & "#"

for CIKSearch being a date.

RexesOperator had it correct in his post. Use the Debugger to check the value of the search string the way you had it and whichever is the appropriate method for you data from above. You should then understand why your getting a error; you're not passing a value.

It works for A97 and above.

 

[Niniel]

Thank you for correcting the Find expression.

However, both with the versions for text and integer - I suppose with numbers in a text field I have to use text, but I tried the integer version just in case - I still get that error on rst.nomatch.

 

[llkhoutx]

rst.Find "[CIK]= Me.CIKSearch"

is not an accepted DAO method for finding records in a dynaset or snapshot.

Use

rst.FindFirst "[CIK]= Me.CIKSearch"

If that doesn't fix your problem, merely checking NoMatch may not be enough, especially if there are no records to search. Accordingly, I use

if rst.EOF and rst.BOF then
  'at least one record exisits in the dynaset/snapshot
else
  'no records exisit in the dynaset/snapshot
end if

The type concantenation to use in building the SQL string is dependant upon whether or not the reference field is (1) bound, or (2) unbound and what the user intends. If its a bound textbox, the controlsource data type controls; if unbound the actual value datatype controls, i.e.

123 in a bound text field would require the quote concantenation or conversion to an appropriate datatype (byte, integer, long integer, etc) number.

123 in an unbound textbox whould not require quote concantenation if an integer was intended. However, if a text string was intended, the quote concancetation would be appropriate.

Crystal clear, I'm sure.

 

[Niniel]

When I replace "Find" with "FindFirst", I get an error on "FindFirst". Looks like my Access doesn't like that command either.

 

[gemma-the-husky]

I think its find thats wrong - i dont think find is a dao method

try rst.findfirst

if its text then
rst.Findfirst "[CIK]= " & chr(34) & Me.CIKSearch & chr(34)

if its numeric then
rst.Findfirst "[CIK]= " & Me.CIKSearch

if its a date then
rst.Findfirst "[CIK]= #" & format(Me.CIKSearch,"long date") & "#"
(the date has to be in US format)

 

[RexesOperator]

This was done for Access 2000 - and it works. I haven't tried it in Access 2002 or later versions. Here is the code on the popup form:

Private Sub cmdGoToCompany_Click()
Dim rst As Recordset
Set rst = Forms![frmName].RecordsetClone
rst.FindFirst "[SearchField]='" & Me![SearchField] & "'"
If rst.NoMatch = False Then
Forms![frmName].Bookmark = rst.Bookmark
End If

End Sub

 

[Niniel]

I used the wizard to create a search combo box directly on the main form, and it created this code:

Private Sub SCIK_AfterUpdate()
' Find the record that matches the control.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ActivityID] = " & Str(Nz(Me![SCIK], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

When I compare that to the examples you have posted, the one thing that sticks out as being different is your declaring rst as recordset vs. the wizard declaring it/rs as object.
Maybe that's the reason why it won't run for me.

 

[gemma-the-husky]

niniel - in your previous post in this thread you were using rst.find instead of rst.findfirst. That would give you an error.

 

[Niniel]

niniel - in your previous post in this thread you were using rst.find instead of rst.findfirst. That would give you an error.

Maybe it should have, but I swear it did not.
What I posted earlier was simply my attempt to customize RO's code for my situation.

The reason why I ended up with "find" is that in the VB editor, the help system did not offer "findfist" after "rst.", but only "find". And it did not offer anything that looked like "nomatch" at all.
When I put those in regardless, the code would always stop at those points.

I have since found a different method that does runs ok [see below], but I'd really like to understand this and get it to work.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmBrowseApplications"

stLinkCriteria = "ActivityID= " & Str(Nz(Me![CIKSearch], 0))
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frmSearch"

 

[gemma-the-husky]

perhaps its a difference between ado compared with dao then. findfirst etc are methods with dao objects

 

[llkhoutx]

It's time to kill this thread or hire a programmer.

 

[Niniel]

Could be.
However, the code generated by the wizard for the comboboxes also uses "findfirst".
And it runs without any errors.