Solved

MS Access - Saving Records on various tabs of a tab control

Posted on 2009-04-06
5
294 Views
Last Modified: 2012-05-06
I have an Access form which has a tab control with 9 tabs.
The entire form is for the input of data associated with one particular sales opportunity.
6 of the 9 tabs populate data into the main salesoppornity table (1 record per sales opportunity).  The other 3 tabs populate 3 other tables.  One is a document attachment table (DocAttachments), one is a phone conversation log table (PhoneLog), and one is a meeting log table (MeetingLOG).
I have a "save and new" command button in the form header which i did similar to the Microsoft Access Sales Pipeline example, whereby hitting this button saves the opportunity, clears the fields, and increments the ID to a new opportunity for input.
What i would like to do, is on the "phone log" tab, have buttons where i could save and increment to a new record, only those fields on that tab (relating to the phone log table).  This is obviously because you could have many phone logs records for a particular sales opportunity.  This would be the same with the Meeting log tab.  I was hoping someone could give me some direction on accomplishing this.
0
Comment
Question by:GTC-KTX
  • 2
5 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 24079158
place the button in the subform and use the codes

private sub Btn_click()
if me.dirty then me.dirty=false 'saves the current record
currentdb.execute "insert into PhoneLog(OpportunityID) values (" & me.parent.opportunityID &")"
me.requery

end sub

this assuming that the PhoneLog table is associated with the "Opportunity" table using the "OpportunityID"




0
 
LVL 4

Expert Comment

by:Rodger
ID: 24079198
Are you not using the AutoNumber for the DocAttachments, PhoneLog, and MeetingLog Tables?  Using the PhoneLog I would create a form with all the info I need then on the main form I would add a subform and link the two with the phoneLogID.  So in the phoneLog table you need a field that will hold the ID (PK) from your main table.  Now when ever a new phoneLog is entered it will update the table with the appropriate ID and what ever information is entered on the form.
Does this help?
Rodger
0
 

Author Comment

by:GTC-KTX
ID: 24079795
Capricorn1,
Placed the code you suggested in the form.  I cant test it yet to see if it works due to a problem i'm having whereby i can see all the controls, etc in my form when in design view, when i toggle to form view everything disappears except the header (this is something i posted in another thread).  Just wanted to let you know i can't test your solution yet until i get that solved in the other thread.

Rodger,
I am using the Autonumber for each of those 3 tables as well as the main opportunity field.
So there is an ID field for each phone record which auto numbers, and likewise there is an ID field for each meeting record which auto numbers.
The Primary key for the main Sales Opportunity also has an ID field that auto numbers.  This Sales Opportunity ID field is also inserted into the other 3 tables as a foreign key.
0
 

Author Comment

by:GTC-KTX
ID: 24098047
Hey Guys, I got my other issue solved so i'm back trying to solve this.
Capricorn, This is the same project that we had worked with the table associations yesterday, so yes, the phone log table is related to the Opportunity table.  The Primary Key (the opportunityID) of the opportunity table is a foreign key on the Phonelog table.  I decided that i wanted to add both record navigation and operation buttons to the phone tab, so using the command button wizard, i placed record navigation, and record operation buttons on the "Phone Log" tab.  As i suspected, even though these buttons are on just one tab, they apply to the entire form.  So if i am on opportunity record ID 5 and phone record ID 1, and hit the record navigation button to go to the next phone record, it increments both the phone record to ID2, and the opportunity record to 6.  Obviously this is incorrect.  I just want these particular buttons to control only that tab (the phonelog table).  I notice that the wizard that i used to place these controls places a macro in the "on click" event:

Condition:                  Action:           Arguments:
                                  OnError              Next,
                               GoToRecord      ,,Next,
[MacroError]<>0     MsgBox            =[MacroError].[Description],Yes, None,

Is this the entire macro?
Is there a way a can modify it to work with only the phone log tab?
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now