forms, subforms, and sub-subforms problems

hilbertm

Registered User.
Local time
Today, 07:52
Joined
Sep 7, 2001
Messages
46
My problem is two fold. First I am not very apt at VBA programming . Second, I have embarked on a project that is quickly getting out of control. I designed a database with one table to track items at work without knowing much about databases. It worked fine at first because I was the only one using it. But now everyone in the office is using it and I am running into all of the problems that are associated with poorly designed databases (having to add fields as requirements change, and user typos being the most annoying).

I am designing a new database with 15 new tables while everyone else in the office uses the old one.

My main stumbling block right now is described below.

I am using the following code to open a form that enters a new record on a cmd button on a subform.
Private Sub cmdPtasNew_Click()
On Error GoTo Err_cmdPtasNew_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmPtasNew"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord acDataForm, stDocName, acNewRec
Forms!frmPtasNew!ProcedureID = Forms![frmHostNationViewProcedure]![ProcedureID]

Exit_cmdPtasNew_Click:
Exit Sub

Err_cmdPtasNew_Click:
MsgBox Err.Description
Resume Exit_cmdPtasNew_Click

End Sub

This code works fine thanks to the help of Rich and others. But I have not been able to come up with the code to open a new form “frmPtasEdit” to edit the data in the current record.

The form to enter new data is called “frmPtasNew” this form opens with a new record for the same ProcedureID.
“frmPtasView” is a form used as a subform on the mainform.
frmHostNationViewProcedure is the main form


Is it possible to use this code (modified slightly of course) to open the same record that is on the subform? Is there a function to replace acNewRec that will open the current record?

I have tried a number of different things, but none have worked so far.

If I get this form/subform problem solved, I can use the technique on 5 or 6 different forms in the program.
Thanks in advance for any help, I’m sure I will be asking for a lot of help in the near future.

Mike H.
 
If you have data such as employee Id that has a one to many relationship on another table(such as Positions the person has held) you can use a combobox to select the employee id. once selected you can use a command button to open a new form with the positions(use a list box based on a query) Once you have that established edit the query to = combo box value. You then get a new form with a list box of different positions. Create a command button that will open a new form based on a query (this query will be the same as the other you will just have a different criteria value). You can then set up text boxes that are bound to the fields you want to edit. This will update that particular position data for the employee of your choice. If this is confusing I can e-mail a sample database to you.
 
kellan4459,
Thanks for the reply.
In this section of the database, the first form opens with a list of instrument procedures at an airport. I have a listbox that lists all instrument procedures at the particular airport. When I double click on an instrument procedure another form opens up with information displayed for that particular instrument procedure. The form has 5 tabs that contain subforms.


Each tab is another set of information for the instrument procedure. The New PTAS button opens up a new form that adds a new PTAS for the current instrument procedure. The code is listed in my original post. This is the button that works.

I would like the Edit This PTAS button allow the user to edit the current PTAS information. (I have the form un-editable because my users make many mistakes without knowing it
smile.gif
)

I am confused on how your solution will work, so maybe you can e mail me your example.
 
First create a query that will pull all the information you want to be able to edit for the current PTAS. Once you get that query create a New Form based on this query. Once you have this form set up you should have all text boxes bound to these fields. Then go back to your query and set it equal to the selected PTAS(Current One you want to edit). Create a button (Edit PTAS) that will call to that form. This will open it for editing. I will send you what I have as an example but yours will be different due to controls you are using.
 
Private Sub cmdPtasEdit_Click()
On Error GoTo Err_cmdPtasEdit_Click
DoCmd.OpenForm "frmPtasEdit", acNormal, "", "[tblPTAS]![PtasNumber]=[Forms]![frmHostNationViewProcedure]![frmPtasViewSubform].[Form]![PtasNumber]", acEdit, acNormal

Exit_cmdPtasEdit_Click:
Exit Sub

Err_cmdPtasEdit_Click:
MsgBox Err.Description
Resume Exit_cmdPtasEdit_Click

End Sub
Suppose a quick trip on an F16 from say the UK to Hawai is out of the question?
 
Rich,
Works now!!! You are my hero
smile.gif

If it were up to me I would give you an F-16, This has been driving me crazy for a number of weeks now.
THANKS,
Mike

P.S.
If you ever find yourself in Hawaii, Lunch is on me.

[This message has been edited by hilbertm (edited 11-28-2001).]
 

Users who are viewing this thread

Back
Top Bottom