Advanced LookUp and Append

[andysgirl8800]

Thanks for your response. I was unable to open the attachment you sent, so I guess you could say this is "deep end" work.

My list boxes are set up with the following information:

Data:
control source......._________
row source type....table/query
row source...........tblBaking
bound column....... 1
default value........__________
validation rule....... __________
validation text...... __________
enabled...............Yes
locked.................No




I couldn't get a double click function to work with the following code:

=Add()

Written in the on "dbl click function" of the listboxA. So I instead attempted the same funtion with a button with the following code:

Private Sub btnAdd_Click()
Dim str As String
  Dim c As Control
    Dim introw As Integer

Set c = Me.listboxA
  str = ""
  
  For introw = 0 To (c.ListCount - 1)
    
    If c.Selected(introw) Then
      str = str & c.Column(1, introw) & ","
    End If
  
  Next introw

str = Left(str, Len(str) - 1)

Me.listboxB.RowSource = str
  Me.listboxB.RowSourceType = "value list"
    Me.listboxB.Requery
End Sub

This is what will move one item over, but only one selection at a time. I can select 4 items and click the button, and these 4 will move over. Selecting a new set of 3 items and clicking the button will delete the previous 4 and insert the new 3. I haven't even begun on a remove function and will get to work on a cascade combo box.

 

[andysgirl8800]

I've read the multitude of posts on cascading combo boxes and read the Microsoft tutorial http://www.fontstuff.com/access/acctut10.htm, but can't seem to make even a simple set up work. I've been working on it for the last week and have made absolutely no progress.

I followed the tutorial step-by-step, but nothing is happening as it should. I've been practicing on a sample database, which I have attached. If you could please take a look and let me know what I am doing wrong?? I'd REALLY be grateful! Thank you.

 

[wiklendt]

This really sucks. i am having trouble unzipping your file. i get the error message "input file does not exist"....

(curious how your firewall allows creating zips but not expanding them! what a weird policy!? where do you work, if you don't mind me asking?)

are you able to post it again?

 

[georgedwilkinson]

I can't unzip it either.

 

[dkinley]

Just breezing through and thought I would give it a go.

I can't unzip and it seems as if it is a multiple-stage zip ... like remember when we used to zip a file across several floppy disks because one disk wasn't enough to hold the file?

-dK

 

[georgedwilkinson]

That's kinda what I thought.

 

[wjburke2]

I do this all the time

In your row source property of the bound control create a query with the two columns you wish to lookup. Make the first column the value you want returned. when you select a category from the list or base it on another field it will return the value into the desired field.

For instance I have two table one called Category the other Product. The category id is returned into the category id field on the product screen via a lookup. I show the user the category name not the number so they. Could you use this to populate the field in question?

 

[andysgirl8800]

GRR! Sorry guys. Think I may have to attempt to zip it on another PC. My work one is awfully stubborn about Zip files. I thought this was just an incoming bias, but apparently it's an outgoing one too. Thanks wjburke2, I will try your suggestion. I did follow the step-by-step instructions in that list, but nothing happened.

 

[dkinley]

One of my client's e-mail security is the same way (ingoing and outgoing). To get around this we zip the file and change the extension to our initials or what-have-you to get it through. Once through, we change it back.

-dK

 

[andysgirl8800]

I'm starting to think it is my AfterUpdate code that is failing.

I have completely redone my form in its simplest format to focus exclusively on this silly cascading combo box. I have followed several different tutorials vebatim, and it STILL isn't working. After creating a few new bald spots on my head now, I'm wondering if something is amiss with this AfterUpdate code. The two combo boxes are correctly set up and my first list of values shows up just as it should. The second, UNBOUND, combo box is correctly set up, with the appropriate column count. I've attached screenshots for reference.

However, when I select something, ANYTHING, from my first combo box, absolutely NOTHING happens in my second combo box. I get no errors, no code corrections, NADA, ZIP!

Here is the code for my AfterUpdate event procedure:

Private Sub cboStoreCategory_AfterUpdate()
With Me![cboItems]
    If IsNull(Me!cboStoreCategory) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [Baking] " & _
                   "FROM tblBaking " & _
                   "WHERE [ID]=" & Me!cboStoreCategory
    End If
    Call .Requery
  End With
End Sub

According to the tutorial, http://bytes.com/forum/thread605958.html, this is supposed to match up the IDs from each table. I've confirmed that my tables are also set up correctly, as you can see with the screen shot. Please note that the field "ID" in the tblBaking is not the autoID, and is only referential where the "IID" is the autoID set by Access.

 

[wjburke2]

It sounds like Baseing on combo box on another

First your rowsource is only the query. The control source is the updated field. Your code updates the Row source but not the actual field and proably gives you a blank field on the screen even though the list has data

I use this code:

' cmbCategory Select Combo Box
Private Sub cmbCategory_AfterUpdate()

Me.lstPrduct = Null ' Clear the list
Me.lstProduct.Requery ' Requery the Product list
Me.lstProduct = Me.lstProduct.ItemData(0) ' Point to first entry

endSub

The query for the product field is:
SELECT [Prod].[PrdId], [Product].[Product], [Product].[ProdType] AS Type
FROM Product
WHERE ([Product].[CatId])=[Forms]![Product Upd]![cmbCategory]);

See Basing one combo box on another at:
http://office.microsoft.com/en-us/access/HA011730581033.aspx

 

[andysgirl8800]

EUREKA!!!!!!!! THANK YOU wjburk2! That was exactly the tool I needed to finish this rotten project! Had to do some relationship building with my tables also, and build in some "not in list" events, but it's all working marvelously now! Thank you to everyone who provided their valuable feedback and programming skills.

 

[wiklendt]

sorry i wasn't more help - i have been really busy and was hoping to really do a "step-by-step" for you on my original suggestion...

however, i am very glad you found you solution and it is working for you. any chance you could post the database here with the solution? (form a non-firewalled computer!) ;-P

 

[andysgirl8800]

I can try to post my dummy DB, which is a shopping list, of all things! It's where I do all my playing around with new things before I transition them over into my production DB, which is too confidential to post here. My dummy DB is really a mess though...

 

[wiklendt]

ha ha ha - my production DB are all messes!! ;-P