Hello,

I recently bought "Expert One-on-One Access Application Development" by Helen Feddema, and I am stuck in chapter 2, pg. 53. I have two tables; tblVendors which is the "One" side of the relationship, and tblVen_Addrs, which is the "Many" side. I created a main single-form based on tblVendors and a subform based on tblVen_Addrs. Following the examples in the book, I created an unbound control, set its rowsource to tblVendors and then wrote the code for the cboSelect_AfterUpdate() as listed on page 53.

However, when I switch to Form View, and attempt to choose a record by using the cboSelect combo box, I get an error message through the "On Error GoTo ErrorHandler" code, which displays the message "Error: 3021 Description: No Current Record". The number changes in the combo box, but all the other fields remain blank.

Also, I'm not sure what I did, but now for some reason I can't see my subform anymore. It is there in design view, but when I switch to Form view, its gone. I checked the properties & made sure that Visible = True; but I don't know what else could be causing this.

Please help!!!

Thanks!

Just to see if I have learned anything from reading parts of this book (and many others) I tried to recontruct the database that you made. I created a tblVendor (Fields: VendorID, Vendor) and a tblVendor_Addrs (Fields: Vendor_AddrsID, Vendor_Addrs, VendorID). I created a relationship to link the two tables (linked by VendorID). I created a form bound to tblVendor and added a subform bound to tblVendor_Addrs.

At this point I was able to open the form and add data to populate the two tables. NOTE: Once I added some data, I could move from Vendor to Vendor in the main form and the data in the subform changed appropriately to display the linked Vendor_Addrs data. Does your form/subform work like that?

Then I added cboSelect, whose record source was tblVendor bound to column 1 (VendorID). I modified the properties of cboSelect to display 2 columns, but I set the width of the first column to 0. Now cboSelect should display the Vendor (text), but hide the VendorID. I added the cboSelect_AfterUpdate code shown on p.53. I deleted the strSearch expression for the text ID and edited the strSearch expression for numeric ID to read:

strSearch = "[VendorID] = " & Me![cboSelect]

When I open the main form and selected a Vendor from cboSelect both the main form and the subform changed to display the appropriate data. The bottom line that this seems to work OK.

You stated that when you attempted to choose a record using cboSelect the number changed in the combo box but all the other fields remained blank. Assuming that you have some data in the tblVendor and tblVendor_Addrs, when you open the form you should see data for the first record in tblVendor displayed on the main form and the appropriate data from tblVendor_Addrs displayed in the subform and the cboSelect text field should be blank. Get your main form/subform workinh correctly without cboSelect and then add cboSelect and its associated code. BTW, the Access control wizard can be used to add a combobox and VBA code very similar to that shown on p.53.

I hope this helps. But if you are still having trouble, I would be willing to send you the simple database that I created. Let me know.

Terry Waltz

Hello tlwaltz,

I'm not sure what I've done wrong, but I wrote some code in the Form_Open Event to to display the record number:

When I open the form, I get the number 0. This does not seem right to me. It seems like the form is not linking to the appropriate table, or is not moving to the first record when opened even though the correct table is listed in the form's RECORDSOURCE property. I think this may be why I'm not seeing any subforms showing, because the form doesn't have a record selected on open. Do you have any suggestions?

Also, I have purposefully disabled the form's NavigationButtons property to prevent users from moving through the database without using the proper methods. And when I click on the cboSelect combo box, I see a drop down list of all the correct record numbers, its only when I try to select one that I get the error message.

The secret to OOP is knowing what the events are, knowing the sequence in which the events are triggered, and understanding what tasks are commonly performed in a particular event.

I think that within the Form_Open event you will get the Me.CurrentRecord=0. Put a statement like

intCurRec=Me.CurrentRecord

in both the Form_Open (add a breakpoint to this line of code) and the Form_Load events. Open the form and step thru the code. Within Form_Open Me.CurrentRecord will equal 0, but withing Form_Load Me.CurrentRecord will equal 1.

I'd start over recreating your form. Go step by step and make sure that each step works OK as you go. First, create the main form and verify that you can display the data in a record and that you can move from record to record. Then I'd add the subform and verify that the data in the subform is synchronized with the data in the main form. Once all of that is working correctly, I'd add the combobox to navigate to a specific record. Then the last thing I would do, once everything is working OK, is remove the navigation bar.

I am curious as to why you are displaying the record number in the combobox. Are you trying to find a particular record using the record number? Usually the combobox is used to find a particular record based on the key field. Record numbers will change when you delete records (record numbers also will change if you filter or order the data).



Terry Waltz

Hi again,

Well, I ended up remaking the form from scratch and now everything works fine...?!?! Don't know why, but it must have been a glitch.... Here's to hoping I don't have any more... =)

Thanks for the help!!!

Terry Waltz's advice was correct. I suspect the problem had to do with the field used for synching the record.

Helen Feddema
Kerhonkson, NY
[email protected]
http://www.helenfeddema.com
Read Woody's Access Watch
http://www.woodyswatch.com/waw/