Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 29th, 2006, 08:45 AM
Authorized User
 
Join Date: Jan 2005
Location: Redford, MI, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Populating master table with list from list table

Another question. I have a form that I am working on.

When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.

When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).



Reply With Quote
  #2 (permalink)  
Old August 29th, 2006, 04:32 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Assuming you have the info in one table with fields along the lines of Section | Question, set the After Update event on the ComboBox for Section to [Event Procedure], click the "..." button to the right, and use code similar to the code below:

Me.comboQuestion.RecordSouce = "SELECT DISTINCT Question FROM tbl WHERE Section = '" & Me.comboSection.Value & "';"

Edit: Then just use set the action for a command button to initiate an Append query for the master table using the values selected, you should be able to google that easily enough.
Reply With Quote
  #3 (permalink)  
Old August 30th, 2006, 07:31 AM
Authorized User
 
Join Date: Jan 2005
Location: Redford, MI, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the info.

I'm having another problem with this as well.

The 2 combo boxes that I have are unbound. I'm trying to complete the above using a subform, with the child/parent link from the subform to the 2nd combo box (2nd combo box filters based on the 1st one). But when I try to create a subform, it won't let me make a connection between the two.

This is getting very frustrating, and my deadline is at the end of the week!!

EEK!

Reply With Quote
  #4 (permalink)  
Old August 30th, 2006, 11:25 AM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You can't use child/parent links to connect to a subform in that manner. You'd need to change the AfterUpdate event on the second combo box to change the RecordSource for the subform to a SQL string that returns the info you're after similar to the string above.

Child/Parent relationships only work between related tables, whereas your effort is more directed at developing a query upon a table.

Reply With Quote
  #5 (permalink)  
Old August 30th, 2006, 11:31 AM
Authorized User
 
Join Date: Jan 2005
Location: Redford, MI, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Again thanks for the info!! I'm sorry to keep asking questions, but my boss seems to think that even though I can set up a database, that I should know how to do all this back-end stuff as well, and I'm still learning a lot!

Can you enlighten me on SQL statements a bit?

If I understand this correctly, I can create a subform, but I can not create a child/parent link to the 2nd combo box. I would need to put some coding in on the After Update under the 2nd combo box. Do I have this right so far?? Would it be along the same lines of the SQL statement from about?

Also, is there a reason in VB when I try to put coding in, (i.e., section.requery), it's giving me errors, and not recognizing the requery?

Reply With Quote
  #6 (permalink)  
Old August 30th, 2006, 03:11 PM
Friend of Wrox
 
Join Date: Dec 2005
Location: , , .
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You seem to have it pretty well under control. The code for the AfterUpdate on the 2nd event would be similar to:

strSection = Me.cboSection.Value
strQuestion = Me.cboQuestion.Value
Me.subfrmName.Form.RecordSource = "SELECT * FROM tbl WHERE Section = '" & strSection & "' AND Question = "' & strQuestion & "';"
Me.subfrmName.Requery

Reply With Quote
  #7 (permalink)  
Old August 31st, 2006, 06:30 AM
Authorized User
 
Join Date: Jan 2005
Location: Redford, MI, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Not too well under control. Here is the coding that I have so far. I'm getting error's on the last code that I'm putting in. It's telling me that the Variable is not defined. sorry for all the questions. (I have a programming class at the end of September).

Code:
Option Compare Database
Option Explicit


Private Sub Combo0_AfterUpdate()
Me!Combo2 = Null
Me!Combo2.Requery

End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click


    DoCmd.Close

Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

End Sub
Private Sub CmdNewQuestion_Click()
On Error GoTo Err_CmdNewQuestion_Click


    DoCmd.GoToRecord , , acNewRec

Exit_CmdNewQuestion_Click:
    Exit Sub

Err_CmdNewQuestion_Click:
    MsgBox Err.Description
    Resume Exit_CmdNewQuestion_Click

End Sub

Private Sub Combo2_AfterUpdate()
strSection = Me.Combo2.Value
StrQuestion = Me.FTQQuestions.Value
Me.sbfrmQuestions3.Form.RecordSource = "SELECT * FROM tblFTQChecklistMaster WHERE Section ='" & strSection & "' AND FTQQuestions = '" & StrQuestion & "';"
Me.sbfrmQuestions3.Requery
End Sub
Any help would be great!

Reply With Quote
  #8 (permalink)  
Old September 1st, 2006, 06:03 AM
Authorized User
 
Join Date: Jan 2005
Location: Redford, MI, USA.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I almost have this form done.

I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.

What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.

I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Populate a List Box with Table Names & Table date hewstone999 Access VBA 1 February 27th, 2008 10:10 AM
STL Linked List or Hash Table Bill_Thompson Visual C++ 0 November 14th, 2007 11:42 PM
updating the table from the list view columns Yasho VB Databases Basics 2 July 31st, 2007 02:06 AM
Updating table from dropdown list GS ASP.NET 2.0 Professional 2 March 18th, 2007 07:22 AM
List Database table through ASP abhisheksud Classic ASP Databases 0 September 5th, 2005 05:10 AM



All times are GMT -4. The time now is 07:42 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.