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.

 

[cgarmas]

Yes! when I open the form I am able to navigate through the IniEncIDs and the subform follows along, the ClientID and EnrollmentNumber in the combo boxes do not show any data while I move through the records, I suppose this is expected.
The relationships are set to Enforce Referential Integrity.

 

[TheAceMan1]

and I'm not sure here on the names of the comboboxes:

[blue]3. Names of combo boxes in Main Form: Combo1= ClientID, Combo2= EnrollmentNumber[/blue]

Have a look at the name property for these to be sure!

See Ya! . . . . . .

 

[cgarmas]

The data type of EnrollmentNumber is text (first, second, etc.)
I renamed the combo boxes to reflect the fields I want to retrieve. Combo1= ClientID, Combo2= EnrollmentNumber
The row source of the combo boxes are still based on the following queries Combo1: SELECT Demographics.ClientID FROM Demographics;
Combo2: 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]![MainForm]![ClientID]));

 

[TheAceMan1]

OK cgarmas . . . . .

In the [blue]AfterUpdate event[/blue] of combobox [blue]EnrollmentNumber[/blue], copy/paste the following:

[blue]   Dim rst As DAO.Recordset, Criteria As String
   
   Set rst = Me.RecordsetClone
   Criteria = "[ClientID]='" & Me!ClientID & "' AND " & _
              "[EnrollNumber]='" & Me!EnrollmentNumber.Column(1) & "'"
   
   rst.FindFirst Criteria
   Me.Bookmark = rst.Bookmark
   
   Set rst = Nothing[/blue]

The code requires [purple]Microsoft DAO 3.6 Object Library[/purple] to run. To [blue]check/install[/blue] the library, in any code window click [blue]Tools[/blue] - [blue]References...[/blue] In the listing find the library and [blue]make sure its checked.[/blue] Then using the up arrow, [purple]push it up as high in priority as it will go[/purple]. Click OK.

[blue]Checkout your comboboxes and let me know . . .[/blue]

See Ya! . . . . . .

 

[cgarmas]

It works wonderfully; you have made a great contribution to developing our project. Thanks a million

 

[cgarmas]

Sorry TheAceMan1 to abuse of your time again, but it looks like I need some more help in finishing with this form. Up to the point where you gave me the last suggestions everything worked fine, I added the second subform and tried entering data and all the forms were perfectly synchronized; then I added a command button on the first subform to open another form that stores information on closing cases. After this addition the cursor now moves from the EnrollmentNumber combo box to the last control in the second subform. This behavior doesn't happen when I enter the first record, it happens when I enter a second record and consecutive once after the first. Please we need advice on how to resolve this problem.

 

[TheAceMan1]

Howdy cgarmas . . . . .

Do me a favor and start a new thread (not addressed to me). I'll be sure to pick it up!

[blue]The reason is so others may benefit from any resolution . . .[/blue]

See Ya! . . . . . .