New Records on Dialog Box (1 Viewer)

fedebrin · Jan 22, 2019

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 · Jan 22, 2019

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:

Code:

Me.VendorID.DefaultValue=Me.OpenArgs

fedebrin · Jan 22, 2019

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 · Jan 22, 2019

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

fedebrin · Jan 22, 2019

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 · Jan 22, 2019

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 · Jan 22, 2019

No, it is not working, sorry

theDBguy · Jan 22, 2019

fedebrin said:
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 · Jan 22, 2019

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 · Jan 23, 2019

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 · Jan 23, 2019

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 · Jan 23, 2019

fedebrin said:
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 · Jan 23, 2019

Good morning,

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

Thanks for all your help!

theDBguy · Jan 23, 2019

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

fedebrin · Jan 23, 2019

Excellent! that code looks very simple and it works!

thanks so much

fedebrin · Jan 23, 2019

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 · Jan 23, 2019

fedebrin said:
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 · Jan 23, 2019

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

thanks!

theDBguy · Jan 23, 2019

Yes. You could try something like:

Code:

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

fedebrin · Jan 23, 2019

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

Code:

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

New Records on Dialog Box (1 Viewer)

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

I’m here to help

Attachments

Registered User.

Registered User.

Attachments

I’m here to help

Registered User.

I’m here to help

Registered User.

Attachments

Similar threads

Users who are viewing this thread