New Records on Dialog Box

[fedebrin]

Hello,

I created a form with vendors which has a button and opens up another form in "dialog ratesheet" format with that vendor's contacts.

The button worked well in that it filters the form with the appropiate VendorID but when I need to enter a new contact, I have to manually enter the VendorID every time.

How can it default to the same VendorID?

I found that this works automatically for Sub Forms but when another form is opened with a button it carries the filter to see only the appropiate Vendor's contact but not the default VendorID

Thanks for your help!

 

[theDBguy]

Hi. You can assign the default value using code when you open the form. For example, you could pass the VendorID using the OpenArgs parameter and then in the Open event, you could try something like:

Me.VendorID.DefaultValue=Me.OpenArgs

 

[fedebrin]

Thanks... I go this far with some VBA (I found this one on the FormsDemo):

Dialog window opens but it is not filtered at all with the correct Vendor


Private Sub Command78_Click()

Const FORMNAME = "Sub_CONTACTS"
Const MESSAGETEXT = "No record."
Dim strCriteria As String

strCriteria = "VendorID = " & Me.VendorID


If Not IsNull(Me.VendorID) Then

Me.Dirty = False

DoCmd.OpenForm "Sub_CONTACTS", _
WhereCondition:=strCriteria, _
WindowMode:=acDialog, _
OpenArgs:=(Me.VendorID)
Else
MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"
End If

End Sub

 

[theDBguy]

Hi. Is the current VendorID value already exists in FormB? I thought you said the button is used to create new records?

 

[fedebrin]

Yes, the VendorID already exists in the DataBase.

We are adding contacts to that vendor which are linked to the vendor by the same VendorID field

 

[theDBguy]

Not sure if we're on the same page yet but try adding the code I suggested earlier in Open event of the opening form.

 

[fedebrin]

No, it is not working, sorry

 

[theDBguy]

No, it is not working, sorry

Would you be able to post a sample db with test data? I’m not sure I am following without seeing what you’re doing or dealing with.

 

[fedebrin]

Sure, sorry about not being clear:

In the DB there are 2 Tables:

1) Vendors
2) Contacts

Both of them have the field VendorID which links them (1 vendor will have many contacts within their organization - one to many relationship - see capture)

Now on the main form for vendors, instead of having a sub_form, I would like for the form "contacts" to open up in dialog mode displaying the contacts for that specific vendor, I managed to do this however when I am to enter a new record on the "contacts" form, the new record does not have by default the VendorID, it obliges me to enter it manually.

Thanks again!

 

[theDBguy]

Hi. Thanks for posting an image of your table relationship but it doesn't help me much to help you since you're trying to fix your form - not your table. I'll need to see how you applied the code I suggested. For example, if you're opening the second form to add a new record, then filtering it to a VendorID may not work if the child table doesn't have a matching record yet.

 

[fedebrin]

Hello,

When the second form is opened to enter the record of a new contact, the VendorID is not defaulted. I have to manually enter it.

The contact form opens and all the contacts are properly filtered though to the corresponding VendorID.

Thanks

 

[theDBguy]

Hello,

When the second form is opened to enter the record of a new contact, the VendorID is not defaulted. I have to manually enter it.

The contact form opens and all the contacts are properly filtered though to the corresponding VendorID.

Thanks

Like I said earlier, you can use code to set the Default Value for VendorID by using code similar to what I posted before. If you can post a copy of your database, we can see where you put it or fix it if it's not working.

 

[fedebrin]

Good morning,

see if you can see the attached Database. I simplified it for this purpose.

Thanks for all your help!

 

[theDBguy]

Hi. Thanks. Check out the attached modified version of your file.

 

[fedebrin]

Excellent! that code looks very simple and it works!

thanks so much

 

[fedebrin]

Oh, one more thing... I realized the VendorID is actually a text value, not a number... how would that code change?

See attached revised database

Thanks!

 

[theDBguy]

Excellent! that code looks very simple and it works!

thanks so much

Hi. You're welcome. Glad we could assist. Good luck with your project.

 

[fedebrin]

could you do the same VBA code but for a VendorID that is text (not number format)

thanks!

 

[theDBguy]

Yes. You could try something like:

Me.VendorID.DefaultValue = """" & Me.OpenArgs & """"

 

[fedebrin]

I tried it as follows and the default VendorID appears but it is not filtering it correctly... see attachment

DoCmd.OpenForm "VendorContacts", acFormDS, , Me.VendorID.DefaultValue = """" & Me.OpenArgs & """", acFormEdit, acWindowNormal, Me.VendorID