data entry/edit form 1

[cgarmas]

Hello all,
I am working on the creation of this database that is taking me a long time because I am not very familiar with Access. Anyway, now the problem is the following: I have a Main form with a subform, which in turns has another subform. These forms are bound to three different tables that are linked in a one to many fashion. The main form is based on a parameter query with three fields fld1 (primary key) prompting for the values of fld2 & fld3 (the values of fld1 & fld2 identify the value on fld1. This opens an existing record on the main form and the two subforms to add data related to this record. Subform1 can add only one record at a time and subform2 can add more than one record for the record on the main form. The problem I have is that when I am done entering data for the current record on subform2, I don't know how to retrieve another record from the Main form to start a new session. The dialog box that prompts for the values for the main form only triggers when I first open the whole form. I am not sure this is the most practical way to go about this or the best, but I will appreciate any help you can give me.

 

[Vittles]

I am assuming that you have many records entered into your main table and are just looking for a way to pull them up again.

If you are continually changing and adding records, why do you have the parameter query? Instead, I would make the main form so that it has all the records or query out those that are not valid/don't need to be seen again via a checkbox field like 'Completed' yes/no.

If you do that, then on the main form you can either search for the record you want using the system search or you can use the combo box wizard to set up a lookup value combo box which would take you to the record after you select the related table value.

The other way to do this is to have a command button that requeries your main form data, but have to say my preference would be the one listed above. The lookup value combo boxes work well to take you to the record you want to see/update in a matter of seconds.

 

[TheAceMan1]

How are ya cgarmas . . . . .

[blue]The problem I have is that when I am done entering data for the current record on subform2, I don't know how to retrieve another record from the Main form to start a new session.[/blue]

[purple]and if you use the navigation buttons for the mainform to goto another record this is not working?[/purple]

See Ya! . . . . . .

 

[cgarmas]

Thank you guys for your answers to my question and sorry I dind't get back to you before but I was away for the holiday.
Answering to Vittles sugestion I have to say that the reason I haven't set up a lookup combo box is because to be able to retrieve a record I have to base the search in the values of two fields, and I don't know how to do that. With those two values I can identify the ID number for that table wich is an autonumber and then pass it to the tables that correspond to the two subforms.
AceMan, when I use the navigation buttons for the main form it takes me only to an empty record, so I need a way to call for another record. I apreciate your great contributions.

 

[Vittles]

Just a couple thoughts, not sure what would be easiest/best. Right now you are querying out (filtering) all other records, so the navigation buttons and such won't work, as when the form opens there is only one record that is pulled up.

You could put an short vba statement in the form On Current Event and then in the After Update events of the 2 fields that have the criteria (these would have to be part of your form, so that you can select different values). That way when you either open the form or change those fields, the form would requery/re-find the appropriate record.

Or you could put a command button on your form to requery the form results based on those fields.

AceMan knows more about VBA than I, so he might be able to give you the best way to do this.

 

[TheAceMan1]

cgarmas . . . . .

It appears you want to lookup records [purple]based on another pair of query parameters )[/purple] (your just not sure how to rerun the query).

Add a Command Button to the mainform and in its [blue]OnClick[/blue] event enter:

[blue]   Me.Requery[/blue]

You can also do this with two comboboxes, setting criteria to the comboboxes instead ([blue]better to see whats there then continually guess[/blue]). The form would open with all records showing (allowing navigation) and a lookup would be performed according to both combo's. It may help you to be able to navigate in the general area.

[purple]Your thoughts?[/purple]

See Ya! . . . . . .

 

[cgarmas]

Thank you AceMan for your reply, and yes I want to find records based on querying a pair of fields in the main form. it doesn't necessarily have to be that way, but that is what I THOUGHT I could do. I hope to help clarify our needs explaining a little more our data collection. We collect information in different stages, and the information that corresponds to the two subforms are collected after the main information is in the database, so when we receive the follow up information for a patient we need to retrieve a record that corresponds to that patient. Unfortunately the PatientId, that is a unique identifier for the patient can be in the main form more that once because people can enroll in the program more than once, so we have another field to identify the enrollment number in the program, so one patient can be enrolled three times and the combination of PatientID and enrollment number can identify an specific record and the unique identifier for the record would be an auto-number that is the primary key for the main table and is the foreign key in the other tables, therefore in the subforms. So we receive the follow up information randomly and that is why I thought about having something to retrieve each needed record based on a query. We would welcome any better ways to do this.
Thank you for your patience and ideas and sorry if I bored you to dead with this long explanation.

 

[TheAceMan1]

Roger That! cgarmas . . . . .

Since both lookup fields are in the same table, one combobox would do. Post the [blue]parameter query[/blue] your using for the main form and I'll hook it up for ya.

If your unsure about using the combobox, the [blue]command button[/blue] I mentioned in my prior post should do.

Have you tried it? . . . Its a quickie. Click cancel when the [blue]command button wizard[/blue] starts and go right to the Click event.

Also:

[blue] . . . that is [purple]a unique identifier[/purple] for the patient [purple]can be in the main form more that once[/purple] because people can enroll in the program more than once, . . .[/blue]

This is indicative of improper [purple]table normalization.[/purple] At your leisure have a look below ([blue]you'll see things alot better and gain those database eyes![/blue]):

Fundamentals of Relational Database Design Normalizing Tables Table Relationships

See Ya! . . . . . .

 

[cgarmas]

Thank you AceMan, here is the parameter query I am using:
SELECT [SWCM Initial Encounter].IniEncID, [SWCM Initial Encounter].ClientID, [SWCM Initial Encounter].EnrollNumber
FROM [SWCM Initial Encounter]
WHERE ((([SWCM Initial Encounter].ClientID)=[Enter Client ID]) AND (([SWCM Initial Encounter].EnrollNumber)=[Enter WHSI Enrollment #]));
and you are right about the table, but I had trouble creating this table in a way that I could identify each patient and their enrollment status splitting this information, so I thought that the best way would be to have both fields in the same table and have an autonumber as primary key in order to define each event of patients and enrollment number. I will take a look at the documents you mentioned, maybe that will help me figure out a more proper way to set up my tables.

 

[cgarmas]

Hello AceMan1,
sorry to abuse of your kindness but I would like to ask for your help on this problem one more time. I reviewed my tables based on the readings you suggested and I split the Main table into two so to have patients entered in one table only once and the enrollment information in another. I also successfully added a Refresh command button to requery the fields in the main form (fld1 and fld2), so far this is working. The problem I am having now is that after the requery when I hit enter on fld2 to go to the first subform, the pointer jumps to the last control in the second subform; when I first open the form it works fine, the problem is after the requery. I've tried everything to get the focus go to the first subform, referencing the forms in many ways and also tried one of your suggestions using a public sub, but had no success. The forms are synchronized because I checked the master/child links and the PKey field from the main table is passed to both subforms. This seems something very simple but I can't seem to find the answer. Thanks for all your help.

 

[TheAceMan1]

Haaaaaay cgarmas . . . . .

I am so sorry! Some how I managed to transfer the Tek-Tip notice for your last post to a folder I rarely look at . . .

Have you checked your [blue]Tab Order[/blue] (MenuBar - View - Tab Order...)?

See Ya! . . . . . .

 

[cgarmas]

Thanks TheAceMan1 for your reply, because I could not resolved this problem I tried with using two unbound combo boxes, which retrieve the values I need with no problem, I also was able to transfer these values to the first subform changing the control source of the field to this kind of expression =Forms!MainForm!Combo2.Column(1), but with this solution I am having another problem: When I go the first field in subform1 to enter data I get a message referring to the next field saying that this next field can not have a null value when I have not even had the focus on that field, and then successively when I move to another field this also refers to the next field and so forth with the same message, I get to go through these warning entering data in all the fields and then when I get to the last field all the data disappears, very bizarre behavior of these forms. I seems like transfering these values to the subform in this way, first the values are not kept or not saved to the table linked to the subform and changes completely the order of the rest of the fields. I checked the tab order within the form and the subform and it's fine. Thanks for your time TheAceMan1.

 

[TheAceMan1]

[blue] . . . I get a message referring to the next field saying that this next field can not have a null value when I have not even had the focus on that field, and then successively when I move to another field this also refers to the next field . . .[/blue]

This does not sound like a system message. [purple]Is there any code behind the subform validating these fields?[/purple]

Is any case I need to be apprised of your new structure before I can make any determinations. Post the tables and relationships, form/subform recordsources and subform link master/child properties. If the sql you provided has changed, post that as well.

See Ya! . . . . . .

 

[cgarmas]

In this new structure I have a main form with two unbound combo boxes, Combo1 based on query: SELECT Demographics.ClientID FROM Demographics;
ClientID (PKey)from table Demographics.
Combo2 based on query: SELECT [SWCM Initial Encounter].IniEncID, [SWCM Initial Encounter].EnrollNumber, [SWCM Initial Encounter].ClientID
FROM Demographics INNER JOIN [SWCM Initial Encounter] ON Demographics.ClientID = [SWCM Initial Encounter].ClientID
WHERE ((([SWCM Initial Encounter].ClientID)=[Forms]![probando]![Combo1]));
lets you select the enrollment number of the patient; and I have a text box field dragged from the field list of the record source (IniEncID), which is the Pkey from table SWCM Initial Encounter, for this field I set the control =Combo2.Column(0) in order to display the IniEncID corresponding to the patient defined by Combo1 and Combo2. The record source of this main form is table SWCM Initial Encounter.
Then I have a subform (FollowUp) wich is bound to table Follow_Up. The property of this form is set to allow data entry refering to the patient identified in the main form. I linked these two forms based on the IniEncID, which is a Fkey in Follow_up.
Retrieving the information into the combo boxes and the text field, works fine, also I have been able to pass the IniEncID to the respective field in the subform, but then the problem I described previously starts. One more thing, I also have the following code in the Subform which I put it in the event I would not be able to link the two forms. I don't really know if this is helping in passing the IniEncID to the subform or just having the master/child link.
Option Compare Database
Option Explicit
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsSubForm(Me) Then
If IsNull(Me.IniEncID) Then
Me.IniEncID = Me.Parent.IniEncID
End If
End If
End Sub

Private Function IsSubForm(frm As Form) As Boolean
' reference to parent form
' fields used for LinkChildField

Dim strName As String
On Error Resume Next
strName = frm.Parent.Name
IsSubForm = (Err.Number = 0)
Err.Clear

End Function

These two forms are single forms, but I also have a second subform nested in the first subform, Subform2 can have multiple records for each record in subform1. Given to the problems I have just between the main form and subform1 I have not gotten to the point to even test how to incorporate this one into the whole structure yet. This second subform would be linked to the first one on the PKey of first subform (FUpID). Sorry for all this lengthy information and once more thank you for your time and advices.

 

[TheAceMan1]

cgarmas . . . . .

It appears your using your comboxes as a combined lookup for the form. Is this correct?
If so, a little code will easily do this for you ([blue]your currently setting IniEncID instead of navigating to that record)[/blue].
If not, please clarify.

[blue]I have a text box field [blue]dragged from the field list[/blue] of the record source (IniEncID), which is the Pkey from table SWCM Initial Encounter, for this field I set the control =Combo2.Column(0) in order to display the IniEncID[/blue]

I hope your aware you just changed a bound control to unbound!

[blue]I have been able to [purple]pass the IniEncID to the respective field[/purple] in the subform . . .[/blue]

Your forms should be linked with [blue]Link Master/Child[/blue] properties and you should remove the code posted (not needed). If linked properly you shouldn't have to pass IniEncID (maybe for a new record . . . depends), when you change record on the mainform, the subform should follow along. After linking the forms proper, [blue]disable the passing of IniEncID[/blue] and make sure you can navigate the form normally with the nav buttons. When you can do this you'll know your set. I'll give you the code for the combo's (lookup) but need a little more info (I havn't forgotton the problem your having, its just first things first).
[ol][li]Post the relationships in the following format (Relationship is [blue]1 to 1[/blue] or [blue]1 to many[/blue]):
[blue]TableName.PKname Relationship TableNameFKname[/blue][/li]
[li]Whats the data types of the PKs and are any autonumber?[/li]
[li]Names of the [blue]name fields[/blue] for client in the Demographics table (can be added to the [blue]RowSource[/blue] of Combo1 so you can see the names)[/li]
[li]Form/subform/combobox actual names[/li][/ol]

See Ya! . . . . . .

 

[cgarmas]

1. Table Demographics -Pk ClientID (characters & numbers).
Table SWMC Initial Encounter -Pk IniEncID (autonumber) -ClientID FK to Demographics table.
Table Follow_Up -PK FUpID (autonumber) - IniEncID FK to SWMC Initial Encounter table.
Table Issues -PK IssuesID (autonumber) - FUpID FK to Follow_Up table

2. Relationships: Demographics > 1 to M > SWCM Initial Encounter.
SWCM Initial Encounter > 1 to M > Follow Up.
Follow Up > 1 to M > Issues.

3. Names of combo boxes in Main Form: Combo1= ClientID, Combo2= EnrollmentNumber, Textbox= IniEncID. The record source of this form is table SWCM Initial Encounter.

3. Subform1= FollowUp, record source is table Follow_Up, I have no unbound controls in this form, this form includes IniEncID as a FK.

4. The only unique identifier for each client is the ClientID; because of confidentiality reasons names are not allowed in the database or any other info that would identify the participants’ identity. Each client can be enrolled in the program more than once, therefore the Follow Up data needs to be linked to ClientID and the corresponding enrollment number in the program. The specifics about each enrollment period is stored in the SWCM Initial Encounter table and there are follow up encounters corresponding to each enrollment and for each follow up multiple issues can be identified. So far, we have collected the demographic information and the Initial Encounter, soon we will start collecting follow ups. Follow ups happen 3 or 4 for each Client/enrollment period. (sorry for making this too long, I just don't want to miss any explanations that can help you in getting a clearer picture of what we are trying to accomplish).

To answer some of your questions and suggestions:

1. yes I am using the combo boxes as a combined lookup (to find the client and enrollment for which info needs to be entered in the Follow Up table.
2. I am aware that I changed the IniEncID control to unbound.
3. I linked the forms with the Master/Child properties and removed the code from the subform.
4. So far the only code I have is a Requery in the AfterUpdate event of Combo1: Me.Combo2.Requery and in Combo2 : Me.FollowUp.Requery to refresh the subform.
5. I enabled the navigation controls.

I hope I was able to provide the information you need. Thanks again.

 

[TheAceMan1]

cgarmas . . . . .

Excellent! . . . . While I work on the code, now that you have the forms linked, using the nav buttons of the mainform does the subform follow along?

See Ya! . . . . . .

 

[cgarmas]

Once I select ClientID and EnrollmentNumber in the combo boxes, automatically the IniEncID text box in the main form fills in as well as in the FollowUp subform. The combo boxes are still based on the queries I mentioned before.
Once a record is selected based on the 2 combos, it remains the same in both forms even if I try to use the navigation butons on the main form, it doesn't change it to a different record. If I choose a different record from the combos the subform remains sinchronized. The two forms are linked based on IniEncID. I tried entering data and I keep getting the message I explained before refering to null data in forthcoming fields.

 

[TheAceMan1]

Woooooooaaa! . . . . Good thing I asked!

[ol][li]Change the [blue]ControlSource[/blue] property of the IniEncID textbox from the expression to [blue]IniEncID[/blue]. It should be selectable from the properties dropdown list. It needs to be this way anyway, besides, if you think about it, you don't want to see what you selected in the combo (other than testing), you what to see the [blue]IniEncID[/blue] in view![/li]
[li]Remove the requery of the subform in the AfterUpdate event of Combo2.[/li][/ol]
When you open the form, just use the nav buttons! The [blue]IniEncID[/blue] textbox should change as you parse thru the records! (as well as the subform) . . . Is this so?

When you set your relationships did you turn on [blue]Enforce Referential Integrity?[/blue] . . . If not, right-click each relationship in the relationship window and make it so.

See Ya! . . . . . .

 

[TheAceMan1]

BTW . . . whats the data type of EnrollmentNumber Text/Numeric? . . . . needed to complete the code which is done!

See Ya! . . . . . .