By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,388 Members | 1,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,388 IT Pros & Developers. It's quick & easy.

Setting record set within a form with multie criteria

P: 52
I am truly stuck and need some help on how to put this form together; any help or suggestions you can provide would be greatly appreciated. (Sorry this one is so long but I believe it is necessary to have this info so you can see what I am attempting.)

I have been tasked with a project where my users want to be able to enter several pieces of data which I am currently tracking within two tables. Please keep in mind that these tables are being expanded regularly (whenever they get an idea) and may not be set up efficiently as they could. Case in point would be my Custom_Code table where we are tracking Project_Name and Element_Name (which is actually a sub Project of the Project_Name). I know we should have split the information into two tables but I was truly in to deep when they dropped this on me. So, as you can guess; I am truly tracking a Many to One within this table for a Project_Name can have several Element Names within. Iím hoping I can work around this, with your help that is.

[IMG]

[/IMG]

I also have a table named Expected_Release which will track the release numbers for the above projects.

[IMG]

[/IMG]

I have created the below form which Iím hoping to be able to use for modifications, creations, and or deletes. The problem Iím having is; how do I populate the form with the information that is narrowed down from the two combo boxes Project_Name and Element_Name?

My form looks like:

[IMG]

[/IMG]

As you can see the Project_Name is a combo box where I have a select behind it populating it with all valid Project_Names from within Custom_Code table. I then want to drill down further therefore I have the Element_Name combo box which I also have a select behind it that only shows records that are associated to the Project_Name they have selected prior. Again the issue I am having is I donít know where to go from here? How do I get the record set to populate the rest of the fields, keying off of the two options they had selected? One thing I should note is that there are Project_Names within the table that do not have any Element_Names associated with them, therefore the Element_Name can be Null.

I thought it would be as easy as writing a select to populate the ID text box within the form but I am unable to figure out how to update this object. And, I am unsure how I could get the form to reset itself even if I was able to update it? (Iím thinking that I am way off base with this concept.)

Any help and or guidance on this project would be greatly appreciated.
Birky
May 29 '07 #1
Share this Question
Share on Google+
1 Reply

P: 52
I was able to figure it out....

Expand|Select|Wrap|Line Numbers
  1. Private Sub Element_Name_AfterUpdate()
  2. Dim strSQL As String
  3. Dim intID As Integer
  4.  
  5.     intID = DLookup("ID", "Custom_Code", "([Project_Name]='" & Me!Project_Name & "') AND ([element_Name]='" & Me!Element_Name & "' )")
  6.     strSQL = "Select * from [custom_code] where [ID] = " & intID
  7.     Me.RecordSource = strSQL
  8.     Me.DataEntry = False
  9.     Me.Requery
  10.  
  11. End Sub
  12.  
May 29 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.