Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Update Form from another form fails after combo box lookup

Posted on 2005-03-03
Medium Priority
Last Modified: 2008-02-01
I have a field on a form that when clicked on causes another form to pop up. You then select a record from the popup
form and it populates two fields on the main form with the following code.

Private Sub Form_Click()
Forms!prospects![StoreStatusdesc] = Me.Description
Forms!prospects![Storestatus] = Me.status
DoCmd.Close acForm, "Prospect Store Status2", acSaveNo
End Sub

This works fine for the first record but  when I use combo box on the main form to lookup another record and try to
pick from the popup form I get the following message. Method 'value' of object'_textbox' failed. The code I use
to lookup a record from the combobox is as follows:

Private Sub storelookup_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.Find "[Plus Provider # (NABP #)] = '" & Me![storelookup] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I can use the navigation buttons to go from record to record and everything works fine. Does anyone have any
idea how to fix this problem.

also I am using an access data project with sqlserver 2000 as a backend.
Question by:iscomp
  • 3
  • 2
LVL 54

Expert Comment

ID: 13457179
Hmm, I would advise to avoid to use special characters (like "#", "(" and ")") and spaces in fieldnames so my field would have looked like "PlusProviderNoNABPno"
Just to be sure, is the "Plus Provider # (NABP #)" a text field, when it's a number you should use:
rs.Find "[Plus Provider # (NABP #)] =" & Me![storelookup]

Finally check that the combo's "Bound Column" is set to this "Plus Provider # (NABP #)" field.


Author Comment

ID: 13458481
The combo box works fine. The problem is that after I use the combo box to navigate to a record (or any other combo
boxes that I use for looking up a record) I can't populate a field on the main form by using a selection from my "popup" form.

By the way I agree with your comments on the naming convention. I inheirited the database  and haven't gotten around to changing the field names.
LVL 54

Accepted Solution

nico5038 earned 1000 total points
ID: 13458561
Hmm, try to change:
Set rs = Me.Recordset.Clone
Set rs = Me.RecordsetClone


Author Comment

ID: 13458694
That worked. Thanks a lot you saved me a lot of headaches.
LVL 54

Expert Comment

ID: 13458720
Glad I could help :-)

Success with the application !


Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question