Limiting Form Data

[Makaveli]

My first post,

I have one form called propertys and another called vendor.
In the form propertys i have a button that opens up the other form called vendor, and like a subform (which i cant use in this case because of the massive amount of data) I have restricted the form vendor to the data being displayed in propertys by using the code at the bottom of this post. (which im no good at)

With this code the vendor form in data entry links the data via the property id field which is a auto/key number to the propertys table.

So when i add new data in the valuation form access relates it back to the property,

however when i have this form in a mode where i can also browse through the data it doesnt just limit it to the property id selected in the property form but it goes through all the data ever entered in the valuation form regardless of propperty id.

Can anyone please help me come up with a soloution where the valuation form is limited via the property selected. Thanks Makaveli


Propertys code:-

Private Sub Vendor_Click()
On Error GoTo Err_Vendor_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "02 Create Vendor"
DoCmd.OpenForm "02 Create Vendor", acNormal, , [Property ID] = " & me.[property id]"
Exit_Vendor_Click:
Exit Sub
Err_Vendor_Click:
MsgBox Err.Description
Resume Exit_Vendor_Click

Vendors code:-
Private Sub Form_beforeUpdate(Cancel As Integer)
Me.[Property ID] = Forms![add-edit-search property]![Property ID]
End Sub

[OBP]

Makaveli, with large amounts of data have you considered using a tabbed mainform and Subforms for your Data entry?
How are you opening the Vendor Form for "browsing"?
If you are using a Command button can't you use the same [Property ID] = " & me.[property id]" filter as the Add data version.
Do you in fact have 2 forms?
One to add adta and one to view data/
If so, you only need one form, the code that you currently use can be made to open a "Viewing" Form in "Add" mode.

[Makaveli]

Thanks for your reply,
Someone on another forum helped me find the answer,

I needed to change the "" in the code,

heres the correct version FEI

docmd.openform "02 create Vendor", acnormal, , "[Property ID]" = ""& me.[Property ID]

I do need to have two seperate forms but due to the vendor form flicking through every record ever entered, i needed to know how to make it run against the property id in the propertys table, before splitting the form into two ie add edit.

I typed the above code manually spaces and things might not be 100%

again thanks for the intrest/help/reply :friends:

[OBP]

So do you still need help.
I would suggest that you do not actually need 2 Forms for Vendors, merely open it either "View/Edit" or "Add" mode.
If you need help on this can you attach to this thread or email a zipped copy of your database, (it does not need to have any data in it)

[Makaveli]

Thanks OBP,

Your right i dont need to forms, i figured it out due to your input, you can change which mode the one form opens in, i changed it to open at a new record and added some navigation buttons to browse or search through the records allowing the user to edit. (which is the easier and more logical way for me)

If i come across anymore problems which I'm bound to i'll make sure to attach the DB and post another thread.

Thanks for all your help (hope i got your name right)
Makaveli