Keeping Parent Key on Child Record 2

[victory92]

I have a form for the parent (bill-to customer) and another form for the child (ship-to customer). This is a one to many relationship.

The billto customer number is the key on the billto file. This number is shown on the form to display and add shipto records.

When I try to add a shipto record, all fields are intialized including the billto number. Therefore, the shipto is not created as a child to the billto - as the billto is zero.

Is there a way I can display the billto info on the shipto add form but not have this information initialized?

Thanks.

 

[SBendBuckeye]

How are the forms related? Is one a subform on the other? If not, do you call the second one from the first one and wait for it to come back or are they independent? A couple examples using representative data are always helpful.

Good LucK!

 

[victory92]

Bear with me as I am new with access and VB.

The forms are not related. There are no subforms.

Here's my latest attempt. From a list, I am trying to open the shipping form in add mode and pass the billto customer number in the openargs. Here's the following code:

stDocName = "CMS Customer Shipping Add"
DoCmd.OpenForm stDocName, , , , acFormAdd, , Me.CustomerNo

CustomerNo is the billto customer number that I want on the shipto record. This appears to be ok.

I'm having trouble receiving the openargs in the shipping add form. The system created the shell for a form open event:

Private Sub Form_Open(Cancel As Integer)

However, the system does not respond when I add another argument:

Private Sub Form_Open(Cancel As Integer, billCust As Long)

Any ideas? I'm surprised that this is so difficult as I'm sure it is often needed. Am I missing a technique that should be used? I can't use subforms as there is too much information to fit on a screen. Also, it is being requested from a list.

Thanks for any thoughts you may have!!

 

[victory92]

Well now I'm totally lost in that the open form command is failing on run time error. Yesterday this was working - but I failed in the open form. Today I'm not making to the open form command.

There has to be an easier way.

 

[SBendBuckeye]

Hey Victory92, no apology necessary, we were all beginners at one time or another. You are definitely on the right track with what you are trying to accomplish. The missing piece is to test for something called OpenArgs. Here are a couple things for you.

A. Modify your call to include acDialog as below. This will pause your calling form code to pause until your add form is either closed or hidden.

stDocName = "CMS Customer Shipping Add"
DoCmd.OpenForm _
stDocName, , , , acFormAdd, acDialog, Me.CustomerNo

B. In your add form, do something like the following.

If Not IsNull(OpenArgs) Then
AddFormControlForCustomerNumber = OpenArgs
End If

C. If you want to restrict the user to only be allowed to add this customer before leaving the screen then add this line to the code in B above.

Me.AllowAdditions = False

Good Luck!

PS. For the future, its always better to describe your real situation. Your last post more accurately described your problem and thus made it easier to help you.

Happy Thanksgiving!

 

[victory92]

Thanks SBendBuckeye for your last response.

I have added the acDialog to my DoCmd.OpenForm as shown under (A) in your reponse. It now looks like this:

DoCmd.OpenForm _
stDocName, , , , acFormAdd, acDialog, Me.CustomerNo

The value of acDialog is 3 when the command is executed. However, I continue to fail as soon as access executes this command.

I am not sure where to put the code listed in (B) -- that is the "If Not IsNull(OpenArg)....." statement. What procedure should this code be in?

The open form is cancelled immediately. The message in the box is:

Run Time Error '2501':
The OpenForm action was canceled

When I select debug on this error, it highlights the DoCmd.OpenForm statement

I believe it does not like the arguments in the Form Open - see below:

Private Sub Form_Open(Cancel As Integer, billCust As Long)

I have no idea of what access wants or is expecting in Form_Open statement.

Thanks for your help!!!!!!!!!!!!!!!!!!

 

[SBendBuckeye]

Vic92,

The code in B. should be in your called program which is CMS Customer Shipping Add. In the form properties, select the On Load event tab let it build a procedure for you. Then paste the above code into it.

The reason your code is blowing up is that you need sqare brackets aroung any string which has embedded spaces. The moral of the story is NEVER use embedded spaces in form names, query names, field names, etc then you don't have to remember to do that. Try this and see if that helps:

stDocName = "[CMS Customer Shipping Add]"

Hope this helps! Good Luck!

 

[victory92]

SBendBuckeye - you're the best!! It worked perfectly. Thanks so much!!!!

I'm not sure I understand your response "C" above. Will you please elaborate on what this does. If I put this code in -- do I need to change to true in the add procedure?

Also - now that I have the Custome number displayed on the screen - it begs for the Customer name to be displayed. What is a better technique - to pass both the number and name in the OpenArgs or to put SQL code in the VB program to look up the customer name?

Is there a way to pass multiple fields in Open Args? Or do I need to concatenate them with a delimiter (perhaps a comma) into one field for the OpenForm command in the Calling program -- and then break them into separate fields in the Called program.

I'm unsure of what techniques are considered better in the industry. I thought of putting the code below into my 2 programs. What are your thoughts? Is there a better way?

Calling Program:
CombinedVar = Me.CustomerNo & "," Me.CustomerName
DoCmd.OpenForm _
stDocName, , , , acFormAdd, acDialog, CombinedVar

Called Program:
If Not IsNull(OpenArgs) Then
AddFormControlForCustomerNumber = OpenArgs
End If

n = instr(AddFormControlForCustomerNumber, ",&quot
CustNo = Left(AddFormControlForCustomerNumber, n - 1)
CustName =
Right(AddFormControlForCustomerNumber,
Len(AddFormControlForCustomerNumber) - n)


Thanks again for all your help!!

 

[SBendBuckeye]

Thanks. If you have both available to you in the calling program I would go ahead and use them. Why look one up again in the new form. You need one more & in your example:

Calling Program:
CombinedVar = Me.CustomerNo & "," & Me.CustomerName

I try to use delimiters that are not frequently used, eg a tilde ~ instead of a comma as you propose. The reason for this is that you could very well have a comma in a company name and then your parsing could possibly break, depending on how it was written.

Regarding point C above, when you open the form in add mode the user can add as many new records as they wish. If you want to restrict them on only adding the current record then use code like point C above. It's been a while since I had to do that and I don't remember exactly where I did it.

I would start by putting it inside the OpenArgs check you are currently doing. If that sets it to false too quickly (eg you can't add even one record) then put it in the form current code similar to the following:

Static intCounter As Integer

If intCounter < 2 then
If intCounter = 0 Then
intCounter = intCounter + 1
Else If intCounter = 1 then
Me.AllowAdditions = False
intCounter = intCounter + 1
End If
End If

You can only pass one argument to OpenArgs so if you need more than one you pass them as a delimited list and parse them in the called program as you have done.

Nicely done! Good Luck on the rest!