I have a form with a couple of subforms. I need to add another subform to display the many side of one of the subforms however they are joined through a junction table. I need people to be able to add and edit records through this form if needed. I am puzzled at how to add this as an editable subform. The subform is an Action Item List and the subform that I need to add to it is a list of Department POCs for each Action Item, there could be more than one. The three tables are: tblActionItemList; primary key, lngActionItemID, tblDepartmentPOCList; primary key, lngDepartmentPOCID, and tblDepartmentPOCDetails; primary key, lngActionItemID and the lngDepartmentPOCID. I will need to do the same thing with the Action Item and the Actionee POC data.
If you need additional information, I will be glad to provide it.
aaghd, create a query based on the Join table. Create a form based on your query.
On the Form create a combo to select the items that are required for entry.
Set form to Continuous Forms mode.
You can add this as a current subform to the as long as the first one is not set to Continuous or datasheet mode.
If you link it via one of the required Items you won't need a combo for it.
I am able to get that far, but what I am having trouble with is when someone has to add a record to that Combo Box. When an entry is made in this form, the Action Item syncronizes with the primary key on the subform (that is technically filled out already because that is the active record in the main subform) and the list of Department POC's is also available in a combo box to choose a POC. That works fine as long as the POC that you want/need is in the underlying table but when it isn't, then you have to add it before you can select it from the combo box. How do I make it possible for the user to add a POC when it is not available through the combo box? Somehow they have to go to the underlying table to add the POC then return to this form where they were and then be able to select the POC.
I hope I am making that clear. Please let me know if I am not.
aaghd, yes that is very clear, you need to use the Combo Box Property Not In List and it's Event procedure.
You have some VBA that takes what they have typed in to the Combo and puts it in the table and also selects it on the Form.
See this thread and the database that I posted in post #7 for how it is done. http://forums.techguy.org/business-applications/737327-auto-populate-form-field.html
OK, now your cookin' I have a couple of questions, first, I can't find anything in your database that refers to the following VBA string, Dim rstDoctors As Object, what is it referring to? That will help me to emulate the string.
Second, this code is written for a single field name. My name is normalized and also has a phone number that would have to be input, so in all, there are three fields besides the primary key which is an autonumber that would need to be entered into the underlying table. Can this code be rewritten to accommodate writing more than one data field to the underlying table? If so, how?
I rewrote the code to accommodate one of my fields, POC's First Name. How does this look? I believe I figured out that the rstDoctor is a variable (storage for a variable), correct?
Private Sub lngDepartmentPOCID_NotInList(NewData As String, Response As Integer)
Dim rstDepartmentPOCFN As Object
Dim strDepartmentPOCFirstName As String
On Error GoTo ErrorHandler
strDepartmentPOCFirstName = MsgBox("Add '" & NewData & "' to the list of Department POC’s?", _
vbQuestion + vbYesNo)
If strDepartmentPOCFirstName = vbYes Then
' Add Department POC FN stored in NewData argument to the Doctors table.
Set rstDepartmentPOCFN = CurrentDb.OpenRecordset("tblDepartmentPOCList")
rstDepartmentPOCFN.AddNew
rstDepartmentPOCFN!DoctorLastNameFirstName = NewData
rstDepartmentPOCFN.Update
Response = acDataErrAdded ' Requery the combo box list.
Else
Response = acDataErrDisplay ' Require the user to select
' an existing Department POC.
End If
No, it didn't. I received an error message; "A problem occurred while Microsoft Office Access was communicating with the OLE Server or ActieX Control. Close the OLE server and restart it outside Microsoft Office Access. Then try the original operation again in Microsoft Office Access." I still think there are some fundamental changes that I need to make to my form before the code will work. As I said in my earlier string, there are three fields that need filled in when someone would add a POC through this form/combo box, POC LN, POC FN, and POC Phone. Right now as you can see in the picture the Department POC is a combo box which includes all three of those fields. How would someone enter the three fields of information? When I start typing the error message appears.
aaghd, sorry I forgot to mention that you need to set the VBA Library Reference to the ADO.
Open the VBA Editor using Alt + F11 and then on the Main Menu>Tools>References and then find and tick Microsoft ActiveX Data Objects Library, it will have a version number between objects and library, you may need to tick Microsoft Access Objects as well.
Hopefully that will fix the problem.
I have to tell you that everything that I have tried so far is not working. There has to be a way of dealilng with an indirect relationship when you want to enter data into a form. Northwind has the same scenario between Products and Orders. If a product is not in the list how are they getting the new product in the table so someone can choose it from the Order Form and the same thing for the customer, how do you get a new customer entered so you can sell them a product? It is a common situation. I am thinking that it shouldn't be this hard. I guess I could create a switchboard like the Northwind has where you go back to the switchboard, open a form and enter the new item in the table, then return to the form and the new data would be available. What other ideas do you have?
I am posting the DB to enhance your ability to troubleshoot my issues.
On the main form; tfrmBureauListMain/Subs there is a subform; qfrmActionItemDataSubOne. I would like to have the ability to view action items as well as use this form, tfrmBureauListMain/Subs, for data input. The problem with using this form for data input is that the subform, qfrmActionItemDataSubOne, will not allow data input of a Department POC for each action item. The subform, qfrmActionItemDataSubOne will not allow data input because it is based on a query. That is what you have been trying to assist me with. As I said everything I have tried so far has failed. This woudl be my preference for users to fill in all the fields on the main form, tfrmBureauListMain/Subs, when a new action item has to be input.
However, using a command button to take the user to the tfrmDepartmentPOCDetails form to enter the Department POC and Action Item data, returning them to the main form with that new data requeried will work too, however, when I try to syncronize the qfrmActionItemDataSubOne with the tfrmDepartmentPOCDetails it won't work. The command button code will take them to the form but won't syncronize to the lngActionItemID.
If you need additional information, please let me know.
I don't have time to tell you how absolutely great you are. Thank you so much. I can't wait till tomorrow to come and see how you did it. I have to emulate the process for the Actionee Person. I may need you to elaborate on what you did. I will let you know.
You are GREAT!
Thanks,
aaghd
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!