• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

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
0
lordofbeond
Asked:
lordofbeond
  • 5
  • 4
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
test this
TestRev.accdb
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now