Forms! where am i going wrong....

[NigelShaw]

Hi,

frustration city over here.

for something that reads so simple, i cannot do it. why?

4 tables
Contacts
Authorizations
Payment Numbers
Payments

all tables are 1 to many relationships with referential integrity.
every contact can have many Authorizations
every authorization can have many payment numbers
many payment numbers can have many payments

all payments numbers created are based on the latest Authorization number.
a single payment number will have numerous payments listed.

i have tried forms with qry, forms with tbl, forms with forms, sub forms, sub sub forms. i can get so far but then everything stops linking together. ive tried VB ( barr it i am better with Excel!! ) to open forms, link forms.

story.
1. my main form will show the current contact & all details.

2. i click a button and want to make a payment to them.

3. a form should open and automatically create a new payment number with a subform ready to receive payment details in a continuous form layout. i can then either press an apply button on the main form to save or a cancel button to completely cancel the payment number.

after this, i wanted to add a button on this form to add a new authorization number and refresh but i need to get over this hurdle first.

Please Please Please can someone guide me? the rest of my project is based like this so if i can understand how this can be done, i can move forward to complete.


Any help would be graciously received so i can at least move in a direction rather than circles!!!!!


many thanks,


Nigel

 

[NigelShaw]

preparing!!!

Having read Monikers response to a previous thread, i am going to prepare myself and wince in the corner ready to receive a possible harsh telling off for not thoroughly looking through the forum first

though in all honesty, i did look through the forum. its all how the specific post is detailed that could potentially be overlooked.

( all said in jest Moniker )

NS

 

[NigelShaw]

Update

Hi,

thought i'd update. not much further ahead. i'm not asking anyone to do this for me you know as i would like to do this myself, just asking for advice or a bit of guidance. to explain a little further-

Contacts Table
ContactsID ( Primary )

Authorization Table
AuthorizeID ( Primary )
ContactsID ( Foreign )

Payment Numbers
PaymentNoID ( Primary )
AuthorizeID ( Foreign )

Payments
PaymentsID ( Primary )
PaymentNoID ( Foreign )

contacts is related to Authorization by ContactsID
Authorization is related to Payment Numbers by AuthorizeID
Payment Numbers is related to Payments PaymentNoID

i created a query on Authorization & Contacts and grouped by "Last" to give me the last created Authorization number and my main form reads this qry.

i created a form based on Payment numbers and included the "lastofAuthorize" field. i used this macro to open the Payment numbers form:

Private Sub MainFormPaymentBtn_Click()
On Error GoTo MainForm_Error

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Payment Numbers"

stLinkCriteria = "[LastOfAuthorizeID]=" & Me![LastOfAuthorizeID]
DoCmd.OpenForm stDocName,,,stLinkCriteria
DoCmd.GotoRecord,,acNewRec


Exit_MainFormPaymentBtn_Click:
Exit Sub

MainForm_Error:
MsgBox Err.Description
Resume Exit_MainFormPaymentBtn_Click

End Sub

my form " Payment Numbers " is a form based on the table with a sub form of Payments. it was created by the autoform so automatically place the subform.

i thought, it would open the payment numbers form based on payment numbers created against the Authorisation number filtered out in the query and the make a new payment number related to the authorization number.

i have tried just about every variation i can think of but always get errors like "cannot make record at this time, a related field is required in ContactID" or something like that. the other biggest problem is with my Payment Numbers. i wanted to automatically create this without having to enter any info so i set the actual payment number to be used as the PaymentNoID. i set the payment date default value to =Date() so that was automatically filled but the record does not get created, it sits on (AutoNumber) so when i go to the payment area, the payment number is not created and i get an unrelated payment entry in the table.

should i enter the date with something like-
( using the original code above and additions in bold)

Private Sub MainFormPaymentBtn_Click()
On Error GoTo MainForm_Error

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Payment Numbers"

stLinkCriteria = "[LastOfAuthorizeID]=" & Me![LastOfAuthorizeID]
DoCmd.OpenForm stDocName,,,stLinkCriteria
DoCmd.GotoRecord,,acNewRec
DateOfPayment.SetFocus
DateOfPayment.Value = Date()


Exit_MainFormPaymentBtn_Click:
Exit Sub

MainForm_Error:
MsgBox Err.Description
Resume Exit_MainFormPaymentBtn_Click

End Sub

and the set the focus onto the subform?

im not sure where i am going wrong but i am all the same. i can post a copy of the database if requested but would rather email it.

if anyone could help me, i would be really grateful.


many thanks,

Nigel

 

[Rich]

Don't use last but max to get the last number, assuming they're sequential

 

[NigelShaw]

Hi,

they are completely random numbers as the number is determined by an outside source. would this solve my issues though?


NS

 

[NigelShaw]

Hi again,

to add to my last post, if i grouped the AuthorizationID to max, that would give me the latest number as they are sequential. i did find though that when grouping with"last", i had to group several items ( date, AuthNo ) for the grouping to work. would it be the same in the "Max" case?

i'll give it a try.

NS

 

[Rich]

they are completely random numbers as the number is determined by an outside source. would this solve my issues though?

How do they get entered into the table then?

 

[NigelShaw]

Hi Rich,

the Authorization gets entered into the table independent of payment numbers & payments. the typical scenario is-

a contact will get an authorization number by way of outside source.
this number will then stick and have many payment numbers with payments until such a time that the Authorization number is changed. the reason for this is a contact will have numerous payments made to them across a month but only 1 record of payments given at the end of that month. the Authorization number determined who much is paid to the contact for example-

payment number 57 Auth No 79345334331

their invoice No Description Amount Paid
1988 bristol £6700 £6400
1993 London £9300 £9000

Payment number 58 Auth No 7935334331

2013 birmingham £16900 £16600
2014 brighton £17000 £16700

Payment number 59 Auth No 9245666453

220 Edinburgh £20000 £20000
222 Glasgow £25000 £25000

and so on.

i have a form set up already to re-Authorize and the qry will pick up the latest Authorization number to make payments against.


regs,

NS

 

[NigelShaw]

OK, a High Coo!

i was just leaving the office and then thought of something so came back to ask the advice. in regard to my above topic of frustration would this work?

my main form hold info taken from a query
i press a button to open up for a new payment.
run a macro. something like:

Private Sub MakeNewPaymentBtn_Click()
On Error GoTo PaymentForm_Error

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Payment Numbers"

ANumber = [LastOfAuthorizationNo].Value ' set a variable to hold the number
PayNum = [PaymentNo] ' set a variable to hold the last payment number

StLinkCriteria = "[ContactID]=" & me![ContactID] ' payment is held to contact
DoCmd.OpenForm stDocName,,,stLinkCriteria
DoCmd.GoToRecord,,acNewRec

[AuthNo].Value = ANumber ' set the field as the later Authorization number
[PaymentNo].Value = PayNum + 1 ' Creating a new number + 1
Forms("Payments")!Form.InvNo.SetFocus ' sets focus to subform field

Exit_MakeNewPaymentBtn_Click:
Exit Sub

PaymentForm_Error:
MsgBox Err.Description
Resume Exit_PaymentForm_Error

End Sub

i will add a new field in the Payment Numbers table to receive the value leaving the PayMentNoID as an ID primary number only.

then on the payment number form, i could have a cancel button. something like:

Private Sub CancelNewPayBtn_Click()

if(me.Dirty = True) Then
Me.Undo
End If

DoCmd.Close acForm, me!"Payment Numbers"

End Sub.

hopefully, i have written this ok. i ve taken a leaf from my old Excel days in creating the variables. if the idea is correct it it could work, then i probably just need to brush up on the code. otherwise, any suggestions?



regards,


NS

 

[rsmonkey]

you could just post your db... would save me having to read all that malark above!

 

[NigelShaw]

could i email instead? save me having to hide information! i can update forum accordingly.

regs,

NS

 

[NigelShaw]

Hi,

Well, my macro worked. the desired form opened without a problem.
( some revision of the code was needed though which i will post for reference ).

i couldn't get the focus onto the subform field though. i looked at various options but nothing worked. i get error messages-
could not find the field " subform name"
could not find the form " subform name "

so i have temporarily made another option of opening a further form to carry out the actual payments in a continuous form and requery the Payment number form on every enrty so it fills in the datasheet view.

regs,

NS