Tech Support Guy banner
Status
Not open for further replies.

Solved: Keying Form Data Through an Indirect Relationship

2K views 15 replies 2 participants last post by  aaghd 
#1 ·
Hello,

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.

Thanks,

aaghd
 
#2 ·
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.
 
#3 ·
Thanks OBP,

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.

Thanks for your help,

aaghd
 
#4 ·
#5 ·
OBP,

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?

Thanks,

aaghd
 
#6 ·
OBP,

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

rstDepartmentPOCFN.Close

Set rstDepartmentPOCFN = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub
 
#8 ·
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.

I really appreciate your time and attention.

Thank you,

aaghd
 
#10 ·
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.
 
#11 ·
Hello OBP,

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?

Thanks,

aaghd
 
#14 ·
Hello OBP,

Wow, does time fly sometimes!

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.

Thank you very much,:)

aaghd
 

Attachments

#16 ·
OBP,

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:);):D
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top