Autofill code not working correct

I'm trying to make this form so that you can select a vendor and filter the parts or select a part and filter the vendors. I have the first part functional but when I try to select a part it wasn't autofilling vendor so I tried this code:
Private Sub cmbPartName_AfterUpdate()
   Me.cmbVendor.RowSource = "SELECT [Vendor ID], [Vendor] FROM [Vendor] WHERE [Vendor ID] = " & Me.cmbPartName
End Sub

Open in new window


Which seems to muck everything up. Am I using the wrong syntax?
Test.accdb
lordofbeondAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this
TestRev.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ncogneatoCommented:
Try this adding a ..Requery after assigning to the .RecordSource, like so:
 
Private Sub cmbPartName_AfterUpdate()
   Me.cmbVendor.RowSource = "SELECT [Vendor ID], [Vendor] FROM [Vendor] WHERE [Vendor ID] = " & Me.cmbPartName
   Me.cmbVendor.Requery
End Sub

Open in new window

0
lordofbeondAuthor Commented:
Caps design works but it does seem to need a re-query. I tried Ncogneatos re-query line in addition to caps code but it wipes out the displayed vendors above the current record.

When I select a part and then select the vendor I need it to be able to continue selecting parts.

I tried this
Private Sub cmbVendor_Exit(Cancel As Integer)
   Me.cmbPartName.RowSource = "SELECT [ProductID], [Item ID], [Item Description], [Vendor ID] FROM [Vendor Item List]"
End Sub

Open in new window


but that counters the filter for when they select vendor first. Any other ideas?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
it is redundant to have a requery when you set the rowsource of the combo box using vba codes.
0
Rey Obrero (Capricorn1)Commented:
<Any other ideas?>

what exactly do you want to do? happen?
0
lordofbeondAuthor Commented:
I want them to be able to select from either. Currently If you try to swap from vendor select to part select (on different records) you have to reset the whole form to reset the rowsources.

I don't know if maybe I should just have a refresh button if they are switching that resets the form
0
lordofbeondAuthor Commented:
EX try to select a part then set the vendor and for the next record select a vendor... all you get is the vendor from above... maybe on exit reset vendor cmbo box?
0
Rey Obrero (Capricorn1)Commented:
<I don't know if maybe I should just have a refresh button if they are switching that resets the form>

that is what you need
private sub cmdRefresh_click()

Me.cmbPartName=""
Me.cmbPartName.RowSource = "SELECT [ProductID], [Item ID], [Item Description], [Vendor ID] FROM [Vendor Item List]"
   Me.cmbVendor=""
   Me.cmbVendor.RowSource = "SELECT [Vendor ID], [Vendor] FROM [Vendor]"

end sub
0
ncogneatoCommented:
So you're saying there are two things that a user should select? (1) vendor and (2) parts? If this is the case, there should be two combo boxes: one for the vendor, and one for the parts.
0
lordofbeondAuthor Commented:
Cap that code didn't work but I tried this and it did

I changed my page load to Exit Partname and made my exit on vendor reset vendor

Private Sub cmbVendor_Exit(Cancel As Integer)
   Me.cmbVendor.RowSource = "SELECT [Vendor ID], [Vendor] FROM Vendor ORDER BY [Vendor]"
End Sub

Private Sub cmbPartName_Exit(Cancel As Integer)
   Me.cmbPartName.RowSource = "SELECT [ProductID], [Item ID], [Item Description],[Vendor ID] FROM [Vendor Item List]"
End Sub

Open in new window

0
lordofbeondAuthor Commented:
Got the main problem fixed and allowed for me to figure out the finer issues
0
ncogneatoCommented:
Congratulations! :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.