tab control subform

[slimjen]

All using access 2003. not really good at designing forms. I have a tab control form. the first tab is the main data entry form to create records. the second tab is a subform to continue of data entry in additional fields based on the record created in the first tab. when users are on the first tab working with the created record switch to the second tab the record they were working on on the first tab appears for continued data entry. The problem is they have to go back to the first tab to select the next record before they can work on the cooresponding record. i put a combo box for them to select the record from the first tab on the subform but it doesn't show up in form view. it only show the datasheet view for the previous worked on record. I've seen forms where the combo box is above the subform but i cant make this happen. pls help

[alansidman]

I would change this.

the first tab is the main data entry form to create records.

Instead of this being on a tab, I would put it on the main form and then make the tabs all subforms of the main form. In this manner, you then have the ability to change the record you want to work on the main form.

Look at this video on subforms, instead of just inserting the subforms on the main form, insert them on the tabs.

http://www.datapigtechnologies.com/f...subforms1.html

Alan

[slimjen]

I did this but there are two many records to scroll through on the main form to get the cooresponding data on the subform. when i try to use a combo for the record from the main form as a drop down it give me an error about cant save the record.

[alansidman]

Probably time for you to post a copy of your db. Grasping at straws without seeing your layout and setup. Sanitize for confidential data.

Alan

[slimjen]

I agree. I need to get this going so I can start on reports tomorrow and uses testing. I have attached a sample db. I included the 4 main tables and queries for the form. I also included the form in question. I have the design that I can live with called Small Pachage Form. The combo box works but I get error msgs. I can't get the two fields next to the combobox invoicenumber called carrierid and carriername how to populate from the form called frm_SP_Invoice. My original idea was to work with tab control but users would have to go back and forth between the tab control to get the record they want to work on. I know I am a mess but please help me clean it up
Thanks

[goodguy]

I looked at your db and found multiple issues:
1. You have not done what Alan asked you to do in post #2 and you confirmed having done in #3.
2. Your second subform has an error; I removed it.
3. The underlying query for the faulty subform in (2) is also faulty: why is the relationship between tbl_SP_Accounting and tbl_SP_Shipment set to 1:1?

I removed the second subform and found that the first one updated fine upon scrolling in the main form. You would see this better if you moved the main form controls out of the tab control as advised. Also, I saw that the Shipments table has records for just two invoices, so maybe that gives the appearance that it is not updating.

Hope this helps.

[slimjen]

Sorry, I had done what Alan suggested but when I created the sample i missed sending that form. I will revised these issues accordingly and post back. I hope to get this to up and running by the end of the day. I really appreciate this feedback. I welcome additional feedback.Thanks again.

[slimjen]

Goodguy; you stated you removed the 2 query and subform. what were the errors pls? I did have a form(small package form in which the main form was not on a tab and i just inserted a subform for shipment. did you get a chance to look at this. I have the invoice number as a combo box. it works but sometimes gives me an error about not being able to update because of creating dup record. the users would just be inputing additional info in the existing records in the subform not creating here. any thoughts. Also, I couldnot figure out why thereis only 1:1 relationship and it would give the option for many.I appreciate your help.

[goodguy]

I have already outlined the errors I found: but after this post, I went over your db again and saw that the problem you are facing is in the Small Package Form. This is another bad practice discouraged by pros: DO NOT have spaces in object names!
I have fixed your combo box update problem in the attached file.

[slimjen]

goodguy; thank you for all your help. I've attached a word doc with screen shots of the continuing error I am receiving. Do you think if I reset all my tables and relationships(take out spaces and __) will this error go away?
thanks again

[goodguy]

The error is not about naming rules; instead it relates to the primary key and index settings in the underlying tables. Check it and fix it. I see a few records where TRACKINGNUMBER field is blank. Could it be part of the problem?

[slimjen]

I will check.
thanks

[slimjen]

I checked these fields and corrected them. The first subform stopped giving me the error. now the second subform is giving me the same error. I went to the table and I have indexes on the tracking number field because it cannot contain dups. this is the only other field in the table other than the prmary key. Any suggestions? Is it because I used the primary key in the query perhaps?

[slimjen]

Got it! Thanks all for your help

[slimjen]

Ok. I am not getting any errors because I changed the indexes to no. I have a combo box on the main for that when the user selects an invoicenumber it pulls up all the tracking numbers record on the first subform to be edited. My problem lays in the second subform. I need to have the users select the related tracking number from the 1st subform in the 2nd subform to finish editting the associate record without creating a duplicate. Please refer to my sample db attachment to see the format. I am no longer using the tab control because my users wanted something more simple. Thanks for your help in advance.