bookmarking 2 subforms

[umair434]

Hi,


I have a main form with 3 subforms in it - These subforms are based on 1 table (which i know is a horrible design idea, but this is how people at my work wanted) -

these subforms are called subreport1, subreport2, subreport3

a user will have to enter data in subreport1 AND subreport3 OR subreport2 AND subreport 3 - as of now, a new record appears when user enters data in the subreport3 AFTER entering data in the subreport1/subreport3

Now i know i could use multiple tabs in 1 subform and do like that, but i know i could bookmark the subforms right? how would I do it?

any ideas?

Also, how can I add a new record based on "on change event of the date text box", so as the user changes the date he can enter new data.


thanks!!!

 

[GinaWhipp]

Truely, I have no idea what you are doing or what you want to accomplish? Where does the bookmarking come into to play? are you saying you don't want the new blank record to appear?

 

[umair434]

Sorry for the confusion, Gina!

If multiple subforms are based on the same table (half of the fields in 1 subform, and half of the fields in another subform), then when you move between subforms (placed on the main form), a new record is added - how do I sync both the subforms to have the same tableID?

I read online that I could "bookmark" the 2 subforms so they have the same tableID, but I don't know what code to put or how to use it - that's why I mentioned bookmarking.

Basically, I'm just looking to Prevent adding a new record when I move between subforms.

hope i'm making some sense

 

[GinaWhipp]

Ummm, okay still not sure I understand. You don't want them to add a new record. So exactly what do you want them to do, just read it?

 

[vbaInet]

If multiple subforms are based on the same table (half of the fields in 1 subform, and half of the fields in another subform), then when you move between subforms (placed on the main form), a new record is added - how do I sync both the subforms to have the same tableID?

It is possible to synch the subforms but perhaps you can explain why you are splitting the fields into seperate subforms. Is it because one of the subform is in Continuous view and the other is in Datasheet or Single view?

I would advise that you use one subform and logically split the fields using a box control.

 

[umair434]

well I had 2 tables: tblEmployees and tblActivities

tblActivities had many fields - each field representing a report

I made 2 subforms - 1 subform representing DailyReports, the other subform representing Adhocs

I couldn't just put all the fields in 1 subform because there were too many controls, so thats why I made a 2nd subform based on the same table and using only "Adhocs" text boxes.

Now when the user logs in, he/she enters data for daily Reports, and then he enters data for 2nd subform (Adhocs). but this causes a new record to be made as the user jumps to 2nd subform. I wanted to put all the numbers under the SAME ActivitiesID

I know I could make a separate table, but my manager wants it like this:

how do I sync both the subforms?

thank you for your patience!

 

[gemma-the-husky]

can you not link the subform to the main form in the normal way.

then it is automatically synched.

 

[vbaInet]

Can we see a screenshot of your form in Form View.

 

[umair434]

here is the screenshot!

thanks guys

 

[umair434]

the two subforms are linked to the main form via EmployeeID (which has been made invisible)

 

[vbaInet]

So where are the subforms you're talking about? Can you provide a screen shot of the subforms both in design view and form view.

 

[umair434]

I have placed the tabs on the main form - within each tab, I placed a subform!!

here are the subforms in design view.

 

[vbaInet]

I see. If you're needing to separate your fields using tabs then perhaps you need to reconsider your tables structure. Just a thought!

Create a query based on the Record source of your Activities table. Copy the SQL statement of the query (i.e. from SQL View) and use this as the Record Source of all your subforms. Each subform must be seperate forms.

Once you've done that let us know.

 

[umair434]

you'r right! I explained the idea of normalization and he bought it. I made a separate table for Adhocs and linked both the subforms with the EmployeeID from tblEmployees - thanks for your help!

Now to problem # 2: As you can see in the first screenshot, I allow the user to enter data. There is a button "Save" which is infact a "Add new record" button with text changed. Here is the situation:

User opens the form. He enters the data, saves the records and closes the form. Now they may come back on the same day to add more information for other reports. For that reason, I used a combo box which pulls the dates added. User can click on the combo box, select the date which populates the text boxes, and they can edit/add records!

the only issue is that my users add records directly in the text boxes and click save when they return on the same day! This causes an extra line of record to be added. I want them to use combo box if they are returning on the same day. I'm just asking for some advice on my form design??

Note: for the combo box function to work, I have to turn the DataEntry Off. so I used "DoCmd.GoToRecord , , acNewRec" on the OnOpen event for every subform!

An Ideal situation would be that the text boxes change according to the DATE TEXT BOX (since im using date() function

i'm sorry for my inability to explain things clearly :S

 

[vbaInet]

You've lost me a bit there. But I think what you're trying to say is that when the form closes, if data has not been entered in any of the fields it should delete that record?

 

[umair434]

sorry for the late reply.

No - what I am trying to say is that if the users come back on the same day then they see the data they had entered before for that date. Now they could edit it as well and it will Overwrite the record for that day (hence no multiple records for the same date).

but if they come back next day then they see a blank record since no records exist for that date. and Now a new record will be made since it's a new date.

hopefully, i explained it better this time.

 

[vbaInet]

How are the controls populated when the combo box is clicked? Have you got code behind the combo box's After Update event?

 

[umair434]

Yeah I do - Here is the code:
Private Sub Combo109_AfterUpdate()

Dim rs As Object
Dim dteDate As Date

Set rs = Me.Recordset.Clone
rs.FindFirst "[ActivitiesID] = " & Nz(Me.Combo109.Column(1), 0)

If rs.NoMatch Then
dteDate = Me.Combo109
DoCmd.GoToRecord , , acNewRec
Me.Combo109 = dteDate
Else
Me.Bookmark = rs.Bookmark
End If
End Sub

Private Sub Combo109_NotInList(NewData As String, Response As Integer)
Me.Combo109 = Null
DoCmd.GoToRecord , , acNewRec
Me![Date] = NewData
Me.Combo109.Requery
Response = acDataErrContinue
End Sub

 

[vbaInet]

So, could you explain clearly what the problem is? In your post #16 you only kinda mentioned what the users do which was still a bit confusing.

 

[GinaWhipp]

Okay, I think I got it... How are the subforms linked to the main form? If it's EmployeeID, it need to be EmployeeID;Date (or the field that holds the date). So no new record until the employee AND the date changes.