Access VBA/Forms how to open in add mode.

[Shakes83]

Hello,

I have a form (frmMain) with two sub forms (SubA and subB). When frmMain loads the textboxes within SubA are populated from a query that returns values from a local table based on whether a certain ID exists in the table. My problem is that if the ID does not exist the query returns no records and consequently the sub form (SubA) is greyed out and is not enabled, or so it seems. However, if the query and in turn SubA returns no records, I'd like the end user to be able to add a new record through form SubA. I've tried writing code for a button so that the button can allow the user to GoTo new record but it did'nt work. My question is:

How can I design SubA either in its onload event or in the onload event of frmMain so that when the query returns no IDs the user is taken to a new blank record so he/she can enter a new ID ?

Cheers,
Al

 

[MajP]

Is the subforms query updateable. In other words, when it does return records could you add additional records. If not why is it not updateable? Describe the subforms query.

Here is a possibility even if not updateable
Private Sub Form_Current()
If Me.subFormName.Form.RecordsetClone.RecordCount = 0 Then
popup a form to add values
or do something to allow additions
End If

End Sub

 

[PHV]

Have you set the SubA's AllowAdditions property to True ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Three possibile examples.

Private Sub Form_Current()
Me.subFrmKids.SourceObject = "subFrmKids"
'Me.subFrmKids.Form.RecordSource = "qryKid"
'Me.subfrmkids.form.allowadditions = False

If Me.subFrmKids.Form.RecordsetClone.RecordCount = 0 Then
Me.subFrmKids.SourceObject = "subFrmKids2"
'Me.subFrmKids.Form.RecordSource = "tblKid"
'Me.subfrmkids.form.allowadditions = true
End If

End Sub

1.Change the actual form in the subform
2.change the recordsource of the subform to something updateable
3. or if like PHV said, the subform does not allow additions then change it to allow additions.

 

[Shakes83]

PHV: If I turn on allow additions, when the form opens now, SubA is not greyed out anymore. But since the first record I am looking at, has no ID returned from the query, even though it is not greyed out, it wont allow data entry. What I need is that if the query returns with no ID then subform SubA should allow the user to create a new record in the table linked to SubA and shuold allow the user to do some data entry. So its still not working.

What is the difference between the following properties of the subform SubA ?

Allow additions
Allow Edits
Data Entry

Cheers,
Al

 

[Shakes83]

MajP :

In the code window, if I type Me.subform name, should it does not show me the subform name in the list of objects on the form. Is there something is wrong there. When I type Me. it should show me the subform name in the list.

Al

 

[Shakes83]

MajP : This code give me the error runtime error 2498 - an expression you entered has the wrong data type. It breaks on the line with the Docmd.openform

Private Sub New_Record_Click()
If Form_ER_DonrContact.RecordsetClone.RecordCount = 0 Then
'.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "popup a form to add values"
DoCmd.OpenForm Form_ER_DonrContact, acNormal, , , acFormAdd, acWindowNormal
'or do something to allow additions
End If

End Sub

 

[MajP]

Form_ER_DonrContact"
It a string

 

[MajP]

You might want to open the form as dialog using the 5th argument of the openform. This way the user can not minimize it and go back to work on the main form. Disregard if the form is already popup and modal.

DoCmd.OpenForm "Form_ER_DonrContact", acNormal, , , acFormAdd, acdialog

 

[Shakes83]

MajP : If I use "Form_ER_DonrContact" instead it says error 2102 the form name does not exist or is mispelled. But I checked and the form name is correct.

 

[Shakes83]

subform SubA is embedded in frmMain and SubA modal and popup are set to no.

 

[MajP]

My guess is that your form is named
"ER_DonrContact"
and in the Project window you see
Form_ER_DonrContact
Humor me and try
"ER_DonrContact"

Or tell me the name as it appears in the database window.

 

[Shakes83]

If subform SubA is already open within frmmain, then is it a problem if I am trying to use this code in a button on frmMain to open the form again ?

Private Sub New_Record_Click()
If Form_ER_DonrContact.RecordsetClone.RecordCount = 0 Then
'.Form.RecordsetClone.RecordCount = 0 Then
MsgBox "popup a form to add values"
DoCmd.OpenForm Form_ER_DonrContact, acNormal, , , acFormAdd, acWindowNormal
'or do something to allow additions
End If

End Sub

 

[Shakes83]

In the database window it is ER_DonrContact. But when I am typing names of forms in code they want to show up as Form_ER_DonrContact and so on.

 

[MajP]

It should not be a problem. You are opening another instance of that form class. Or you may want to open a new form designed just for data entry.

 

[MajP]

That is something different. A forms name is a string. The class by default gets the form_ prefix. Do command needs a name which is a property of the class.

 

[Shakes83]

Ok MajP you were right, that click event now works and it pops up the form in modal format. But I still cannot type in an ID in my textbox through data entry. In frmMain the subform SubA is pulling the ID if it exists for the record from the query which has certain criteria in it. If an Id does exist, the form works perfectly, I can edit a record or Add a new record. However, in the case that a record exists for frmMain but not SubA then subA seems to be locked somehow. It wont even let me click on the new record * in the navigational bar to create a new record. The moveleft and moveright buttons are there but the new record button * is greyed out.

 

[Shakes83]

Ok then I guess what I need to do is open a new form for data entry but then I will need to refresh frmMain somehow to reflect the new entry in the underlying able which means when frmMain gets refreshed, SubA should get refreshed and show the newly entered ID through the underlying query.

 

[Shakes83]

how do I do that MajP ?

 

[MajP]

Is the subform linked to the mainform or is it a parameter query? Could you post the Sql of the subforms query?