VBA trouble opening form for newly added record (1 Viewer)

[Apples241]

I’m creating a database for the first time and am new to VBA Access, please.

When I click a button on my subform (which is in continuous view) it opens another form, Node Setting Form ("popfrmNEWNodeSetting2" in the code below). The pop up Node Setting Form will open blank with no fields for newly added records to the originating sub form. Can someone help, please?

I’ve been working on the code below and it will open the Node Setting Form for an existing record as Read Only, and it will End Sub for a record on the subform that is blank. But on newly added records in the subform, it will open the Node Setting Form as blank.

I want it to save the newly added record (the 'dirty record', I believe it's called) from the subform and open the Node Setting Form in edit/add record mode so the user can add the settings for the node from the subform. I have trouble with IF, ELSEIF, ELSE just in general, anyway, so that doesn’t help matters.

(I tried adding links to pics of the main/subform and the Node Setting form but it's not working. I think I need to post more before I'm able to.)

I would appreciate any help. Thanks.

Private Sub cmdViewNodeSettingsFrm_Click()

    If Me.Dirty Then
       ‘added Me.Dirty = False b/c thought it saves a ‘dirty record’	
        Me.Dirty = False
        DoCmd.OpenForm "popfrmNEWNodeSetting2", acNormal, , "[NodeID]=" & [NodeID], acFormAdd, acWindowNormal
        
    ElseIf IsNull(Me.NodeID) Then
        Exit Sub
        
    Else
        DoCmd.OpenForm "popfrmNEWNodeSetting2", acNormal, , "[NodeID]=" & [NodeID], acFormReadOnly, acWindowNormal
    
    End If

End Sub

 

[Grumm]

I suppose [NodeID] is the Id of the node you just tried to save ? Not sure if get the correct value there if you force the dirty to update.
Have you tried to refresh the data just before opening the form ?

 

[Apples241]

I suppose [NodeID] is the Id of the node you just tried to save ? Not sure if get the correct value there if you force the dirty to update.
Have you tried to refresh the data just before opening the form ?

Hello,

--Yes, NodeID is the ID of the node I tried to save in the subform.

--Do you know the code save the record if it's not force the dirty to update? I would like to have the command button to do 2 things to a new record: save it and open the pop up form in edit/add mode.

--I'm not sure what you mean by refresh the data, but I did try saving the record as you normally would and even closing the form and reopening it, but the pop up form for that record still shows up blank.

Can you or someone help with the code? I'm so close, I feel. Here is the link to the main/subform pic I had trouble with earlier.

https://drive.google.com/open?id=0B9IyKJSJ52ghamVncDJrbDM1VlU

Pop Up Node Setting form (please ignore gray boxes):https://drive.google.com/open?id=0B9IyKJSJ52ghN0tKV2M4TExSNHM

Thank you!

 

[Apples241]

Just checking back to see if anyone else has any help?

I'm trying to get Access to not open a pop up form when the record in the continuous subform is blank, open the pop up in read only when the record in the subform already exists, and open the pop up in add/edit when the record in the subform is newly added.

It works except for the newly added records.

The command button to open the pop up form is in the continuous subform so it appears next to each record in the subform.

Thank you for any help.

 

[MarkK]

I want it to save the newly added record (the 'dirty record', I believe it's called)

On the down side, 'Dirty' is true for any record in the form that has been edited and not saved, so this will not identify your newly added record. And in fact there is no way to find your newly added record unless you save it first with the date/time, and then check if the current date/time is within a few seconds, which you could do, but there is probably a simpler way.

On the up side, in all cases your record already exists, so there is no need to open the node form with the acFormAdd parameter present.

I would just do something like this, which saves pending edits and opens the form in edit mode...

Private Sub cmdViewNodeSettingsFrm_Click()
    if not isnull(me.nodeid) then
        me.dirty = false
        DoCmd.OpenForm "popfrmNEWNodeSetting2", acNormal, , "NodeID = " & Me.NodeID
    end if
End Sub

 

[Apples241]

Thank you, MarkK.

I added the code you listed and this time my Node Settings pop up form has the field text boxes on it with the labels (so it's not completely blank), but the values are missing.
https://drive.google.com/open?id=0B9IyKJSJ52ghY3lEbDhoS044U0E

I see a new record that I just added in the subform in the underlying subform's table, but when I unhide the Node ID on the subform it shows the new Node ID but on the pop up form the Node ID is 0.

Somehow it's not saving it. Do I start the On Click with a Me.Refresh or Me. Requery?

I appreciate you or anyone's help.

 

[MarkK]

I would expect the line ...

Me.Dirty = False

... to save the pending edits in the current record.

But maybe what makes more sense for what you are doing is this...

Private Sub cmdViewNodeSettingsFrm_Click()
    Dim mode As AcFormOpenDataMode
    Dim filter As String
    
    If IsNull(Me.NodeID) Then
        mode = acFormAdd
    Else
        Me.Refresh
        mode = acFormEdit
        filter = "NodeID = " & Me.NodeID
    End If
    
    DoCmd.OpenForm "popfrmNEWNodeSetting2", , , filter, mode, acDialog
    Me.Requery

End Sub

See what that does? It always opens the popup, but depending on the current record we selectively open it to do what we need. If the current row is a new row, we expect the user wants to add a record, so we open the popup in add mode. On the other hand, if the NodeID already exists in the current row, then we save pending edits (Me.Refresh) and open the popup to the currently selected node.

In addition, the Popup is opened as acDialog, in which case code pauses execution until the popup is closed. At that point we do Me.Requery to show changes in the current form that may have been made in the popup.

Does that all make sense? Can you apply that to your case?

 

[Apples241]

MarkK, Let me look into this tomorrow b/c I forgot that if it's a new record, I will need to add the values to the blank form, but I do need still need the new record's pop up to open to the same node as the originating subform. I will look at this tomorrow and let you know what happens.

Thank you!

 

[Apples241]

I'm sorry, I might be able to try it today, but I will aim to try and reply tomorrow. Thank you!

 

[Apples241]

Sorry for the delayed reply; I did nothing this weekend. I need 3 conditions, but I think I will just leave it with 2 conditions as you have it. I did want the pop up to be read only if it was not a newly added record, but I think you were talking about the time stamp thing.

You have fixed the problem of the pop up form being entirely blank; I now have fields that I can add the info I need to for the newly added nodes on my other form.

The problem now is that the newly added record in the subform will show up and have a Node ID, but the Node ID and corresponding node names will not show up in the pop up form for that record.

In the picture the pop up form should have Node Names BE0404 and BE0505 in the Node A and Node B boxes to correspond to those same node names in the originating subform and the Node IDs should be the same, but the subform says 18 in the gray box and says 0 in the white box next to the empty yellow node name boxes on the pop up: https://drive.google.com/file/d/0B9IyKJSJ52ghZ1ZXLVFiTHVNdzg/view?usp=sharing

I'm reading about default values and saw where someone said to set their default value to Forms(stDocName)![CaseNo].DefaultValue = "'" & Me![CaseNo] & "'" but I am studying it now.

If you or anyone know anything that could help, it would help b/c that is my final problem re: this issue.

Thank you for all of your help.

 

[MarkK]

The problem now is that the newly added record in the subform will show up and have a Node ID, but the Node ID and corresponding node names will not show up in the pop up form for that record.

And what is the code that opens the popup in this case?

 

[Apples241]

I'm using the code you last provided, the one from 12/8.The code is behind an On Click event of a command button, I call cmdViewNodeSettingsFrm. And the command button (it looks like text b/c I made the background of it transparent) lies in the subform. You can see it in the pic where it says, 'View Node Settings'.

I also have code below on an On Current event in the main form but that is to help the subform open to the right node IDs when I select the corresponding hub the from a combo box on the main form.

Private Sub Form_Current()
Me.cboHub.Value = Me.HubID.Value
End Sub

Thank you.

 

[MarkK]

I don't understand the problem. Perhaps you can post a database that demonstrates it. I don't see how a record with a unique ID exists in a subform, but when you open a popup to show that same record, there is different data. I don't know how to troubleshoot that.

 

[Apples241]

Hello,

I've uploaded the database. Thank you for helping.

I've tried some other steps below and, per the classes I'm taking, these should work, but the command button to open the pop up form is not opening the Node Setting pop up form to the corresponding Node from the subform.

The pop up form will open to the corresponding node from the subform for those records in the subform that are existing/already there when I built the forms. But the pop up form will open to be blank for those records in the subform that are newly added.

Key for database attached:
*Copy Of frmNEWNode is the subform
*Copy Of frmScheduleNEWHub is the main form with the subform
*(I don't think frmNEWHub or frmNEWNode do anything, but I don't know, sorry.)
*popfrmNEWNodeSetting2 is the pop up Node Setting form

*tblNEWHub is the basis of the main form
*tblNEWNode is the basis of the subform
*tblNEWNodeSetting is the basis of the Node Setting pop up
(I need to clean these tables up--this is just a test db)

A hub is a city and has many nodes, where each node has specific dates, settings, etc.

I don't know what else to do. We've tried my code, your code, I've tried below. Anyway, thank you, again.

1. 
   I've removed the code you worked on that was on the On Click Event of the 'View Node Settings' command button on the subform.

• I've created a new test command button called 'Open Form' that was created by just using the command button wizard to open the Node Setting pop up form to a specific record.

• I've created two gray combo boxes on the Node Settings pop up form and they are based on the NodeID from the subform that show the node name, Node A and Node B. They are locked. (I did have =[Forms]![Copy Of frmNEWNode]![NodeID] in the Default Value property of each of the combo boxes thinking that would open the pop up form to the corresponding node originating from the subform. [Copy Of frmNEWNode] is the name of the subform object.

 

[MarkK]

In the database you posted the subform "Copy of frmNewNode" has a button called "cmdOpenSetting", but that button runs an embedded macro, not VBA code. Can you explain how to reproduce the problem?

I ran a search for the form name "popfrmNEWNodeSetting2" and all VBA code that opens that form is commented out.

 

[Apples241]

Hello, MarkK. Yes, when I added the new cmdOpenSetting button (the "Open Form" button on the subform), I used the command button wizard, which embeds a macro in the form. I'm sorry b/c I should have mentioned the embedded macro more explicitly. I mentioned that I've removed the code you worked on that was on the On Click Event of the 'View Node Settings' command button on the subform.

I took out the VBA b/c I need simplify things to see why the node names, Node A and Node B (the yellow boxes), are not autopopulating in the Node Setting pop up form with their corresponding names from the subform. I removed all the VBA and just added a button using the wizard to keep it simple in order to fix this problem.


If you click on the "Open Form" button for the first record in the subform for each hub, you will see the Node Setting pop up form with the Node A and Node B node names autopopulated with their corresponding record from the subform. If you click the "Open Form" button for the 2nd or 3rd records that I newly added as a test to the subform, you will see the Node Setting pop up form open with the text boxes blank, which is fine b/c I need to add the info, but the Node A and Node B fields don't autopopulate with the node names from the originating subform record.

You fixed part of my original problem where the Node Setting pop up form was popping up completely blank, with no text boxes, etc. But now the Node A and Node B fields in the Node Setting pop up form not autopopulating.

I've tried different measures seen in my last post, including adding the Refresh VBA command, but it's not working and I am hoping that you or someone can help. Thank you for your help.

 

[Apples241]

I don't know if you are still assisting with this issue, MarkK, but I've opened the pop up Node Settings form and selected the NodeID field on there and put the path below in the default value property of that field. (BTW, Please let me know if you have halted on this project, so I can close it out. You have helped at least have the fields appear in the pop up form, so if you can't help anymore, I'm going to close this and repost somewhere and ask help for my other issue below. Either way, thank you for your help.)


=Forms!MainFormName!SubformName.Form!NodeID


Now the node setting pop up form is populating the NodeID field, but I can't make combo boxes out of the NodeID field to show the Node A and Node B node names from the subform(the Node table has NodeID, NodeA, and NodeB as its fields). In other words, it will pull the NodeID field but I can't get the node names to appear in a combo box using that NodeID.

I've also tried making a junction table and basing the node setting pop up form on that to make a many to many relationship between the node subform and the node setting pop up form, but that doesn't help either.

 

[MarkK]

The problem, IMO, is that the subform and the popup form do not draw data from the same table. As a result, it's not clear to me what it means to open the popup from the subform. What is the relationship between the data in the subform, and the data in the popup? Why are there so many fields in the two tables with the same names?

 

[Apples241]

The problem, IMO, is that the subform and the popup form do not draw data from the same table. That is true, I created a separate NodeSettting table that the Node Setting pop up form (NSPU) is based on. This table has NodeSettingID, NodeID, NodeA, NodeB, and some foreign key ID fields for some separate setting tables.

As a result, it's not clear to me what it means to open the popup from the subform. I say that b/c I mean that I have a command button on the subform that pops up the NSPU form. That's what I mean by "to open the popup from the subform".

What is the relationship between the data in the subform, and the data in the popup? I think it is a many to many relationship. A node can have many settings and a setting can have many nodes. The subform is based on the Node table, which has the
NodeID (PK)
Node A
Node B
Date of Install
Comments
and a couple of foreign key ID fields for a couple of other tables.

I created a separate NodeSettting table that the NSPU form is based on. This table has the
NodeSettingID (PK)
NodeID
NodeA
NodeB
and some foreign key ID fields for some separate setting tables.

Why are there so many fields in the two tables with the same names? It was a mistake. There shouldn't be.

Thank you.

 

[MarkK]

So before working on what form opens what form, we need to normalize the data structures. In this regard, having two tables with almost exactly the same fields is a mistake that needs to be solved first. What are the things you are concerned about here? Hub? Node? Setting? How are they related to each other? Where does each data point belong? Those questions need answers before we work on the details of how to open forms and save records.