Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old June 8th, 2004, 07:52 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subform combo box insanity

Hi All

I might have missed a chapter or what I'm trying t do is just not possible, but here we go:
I've got a Subform in datasheet view, based on a temporary table with about 100 records, where the left column is part #'s and in the 5th column I've got pallet Id's.
What I am hoping to achieve is to have a combo box on the pallet Id for each record, that will look up pallet Id's matching the part # in the left field.
The lookup SQL is based on a different table.
Basically when the temporary table is written, it automatically chooses an available pallet, but I'd like the operator to be able to choose a different pallet for various reasons.
What I've got so far is a combo box that looks up all pallets for all part #'s on the left, rather than the part # in the same record.

Is this possible to achieve or am I dreaming?

Any help would be greatly appreciated.

Thanks / Fred

  #2 (permalink)  
Old June 9th, 2004, 02:44 AM
Authorized User
 
Join Date: Jul 2003
Location: Stockton on Tees, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I would try setting an OnFocus event on the Combobox control. This event then creates the RowSource for its lookup based on the current value of the part #.

OnFocus event code segment e.g.
strSQL = "SELECT [part#] FROM LookupTable WHERE [part#] = " & Me.part#

(if part# is numeric)

Me.palletID.Rowsource = strSQL
Me.palletID.Requery


Cheers Ray
  #3 (permalink)  
Old June 9th, 2004, 02:59 AM
Authorized User
 
Join Date: Jul 2003
Location: Stockton on Tees, , United Kingdom.
Posts: 73
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Small mod. to previous rushed reply:

On Got Focus event code segment: e.g.
strSQL = "SELECT palletID, part# FROM LookupTable WHERE part# = " & Me.part#

(if part# is numeric)

Me.palletID.Rowsource = strSQL
Me.palletID.Requery


Cheers Ray
  #4 (permalink)  
Old June 9th, 2004, 07:14 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Ray

Thanks for your help, but part# is text.
I tried your code but I get a "Data type missmatch in expression" msg.
Since I'm very fresh on this, do you know what to do to get the right data type for the query to work.

Many thanks.
Cheers / Fred

  #5 (permalink)  
Old June 9th, 2004, 07:31 PM
Authorized User
 
Join Date: Nov 2003
Location: Commerce Twp, MI, USA.
Posts: 27
Thanks: 0
Thanked 0 Times in 0 Posts
Default

If part# is a text string, you need to enclose it in quotes. Change it to read as follows:

strSQL = "SELECT palletID, part# FROM LookupTable WHERE part# = """ & Me.part# & """"

Darrell L. Embrey
  #6 (permalink)  
Old June 9th, 2004, 07:37 PM
Authorized User
 
Join Date: Apr 2004
Location: , , .
Posts: 23
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Many many many many thanks. Phew... it's working.
Again thank you both so much.

Cheers heaps / Fred




Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Count in combo box(display results in text box) mboyisis Access 4 April 4th, 2008 07:08 AM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
filtering records on subform from an unbound combo jd_erd Access 3 February 12th, 2007 11:21 PM
Subform combo box 'reverse lookup?' russ2rsa Access 2 June 12th, 2006 08:26 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.