Open a Form to Add Related Records

[wvmbark]

I am working through "MS Access 2000 VBA Fundamentals" by Callahan and have hit a road block. So, I'm hoping someone can help me out.

The database I've built to test this function has two tables:
tblSubscribers: SubscriberID(PK), LastName
tblPayments: PaymentID(PK), SubscriberID(FK), PaymentDate

I have two forms – one bound to each table:
frmSubscribers and frmEnterPayments

frmSubscribers has a command button that opens frmEnterPayments.

When frmEnterPayments opens, it has the SubscriberID already filled via the following code in the On Open event.

Private Sub Form_Open(Cancel As Integer)
' Set the default to the current subscriber.

If IsOpen("frmSubscribers") Then
SubscriberID.DefaultValue = Forms!frmSubscribers!SubscriberID

End If

End Sub


OnOpen function is in a misc. module:

Public Function IsOpen(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view.

Const conDesignView = 0
Const conObjStateClosed = 0

IsOpen = False
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> _
conObjStateClosed Then

If Forms(strFormName).CurrentView <> conDesignView Then
IsOpen = True
End If
End If
End Function

Here's my problem - The frmEnterPayments form does not open correctly if I set the SubscriberID field type to text. In my real application, where I want to use this, my primary key is a text field. If I change it to autonumber, it works fine. Can someone help me out?

Thanks,

Mike

 

[Rolliee]

Add your text field but do not try to make an autonumber anything but a long integer. Keep the autonumber and make your text a primary key.


Rollie E

 

[wvmbark]

In my real application, I want the primary key field (SubscriberID in the test database) to be a text field, not a number field.

If I recreate the above database using a text field as the primary key(no autonumbers), the frmEnterPayment opens not related to frmSubscribers.

If its created with autonumber fields, as above, everything works fine. I don't understand why the field type is effecting the functionality.

Mike

 

[Rolliee]

If you wish your recordset to open in some order, you must specify that order. The data for the forms you describe above is a recordset. You can see that recordset if you go to the design mode, right click on the hidden button at the upper left of the form and look at the properties. One of them is the data source. Double click on that data source and you will see something like

"SELECT * FROM tblSubscribers WHERE <critera> ORDER BY SubID;"

The ORDER BY you will have to add.

Rollie E

 

[wvmbark]

Why does it work perfectly with joined autonumber fields and does not work with joined text fields?

 

[RoyVidar]

What exactly isn't working?

There's no problems using text pk. But I don't understand where your problem arises. Do you rely upon the defaultvalue property settings? Then try something like this:

[tt] SubscriberID.DefaultValue = chr$(34) & Forms!frmSubscribers!SubscriberID & chr$(34)[/tt]

But, if that is the current challenge, I'd probably use the openargs arguement when opening, and the on current event of the form for assigning.

Is there any errormessages, which line does the error occur on...

Roy-Vidar

 

[willir]

Mike

The frmEnterPayments form does not open correctly if I set the SubscriberID field type to text

If I recreate the above database using a text field as the primary key(no autonumbers), the frmEnterPayment opens not related to frmSubscribers

I am assuming you are saying that when you are using the text fields, the Payment form does not open to the correct record that should match the current record in the Subscriber form.

Or are you saying the default value for the SubScriber is incorrect?

SubscriberID.DefaultValue = Forms!frmSubscribers!SubscriberID

If the this code resides in the Payment form, try the following...

Me.SubscriberID = Forms!frmSubscribers!SubscriberID

You may have to repeat this code and/or use the
Me.SubscriberID.Requery
method

Why your code works with autonumber and not when you are using a text field - hard to say - If I were to guess, it might have something to do with how autonumbers are created...

Autonumbers are created has soon as you start the Insert record event - as soon as you start typing in a record. This number becomes available at this time.

With a text entry, the record has not been entered. When you loose focus from one form, the record is either inserted, or remains in edit mode depending on conditions. Is the record still in edit mode in the Subscriber form, so the text value is not accessible from the other form? If so, try a requery of the SubscriberID.

Forms!frmSubscribers!SubscriberID.Requery
Me.SubscriberID = Forms!frmSubscribers!SubscriberID

Richard

 

[wvmbark]

When I use an autonumber as the primary key (SubscriberID):

I open frmSubscribers, go to a particular record, and then click the command button to open frmEnterPayments. When frm EnterPayments opens, the SubscriberID (for the current record in frmSubscribers) is displayed. I can enter new records and SubscriberID remains set to the current records SubscriberID on frmSubscribers.


When I use a text field as the primary key (SubscriberID):

I open frmSubscribers, go to a particular record, and then click the command button to open frmEnterPayments. When frm EnterPayments opens, the SubscriberID (for the current record in frmSubscribers) displays "0" if frmSubscribers is on the first record, "-1" if on the second record, "-2" if on the third record, etc. If I try to enter a new record on frmEnterPayments I get an error message that states "You can not add or change a record because a related record is required in table 'tblSubscribers'. Note, if I open frmEnterPayment with frmSubscribers closed, I can enter a SubscriberID (already saved in tblSubscribers) and it works fine.

The only difference between the two secenarios is the field type for SubscriberID.

Mike