drop down select from another table

[pyromaniac]

I had 3 drop down filters that worked when the word being filtered was written in the table. I have since then linked them to new tables to be more database-y. The problem I have found (a couple weeks later too) is that my drop downs broke.

It makes sense why, they are now 1,2,3,4 instead of words but how do I make this work now that it isn't in the current table?

Private Sub cboMDS_AfterUpdate()

Dim myPlane As String
If Me.cboMDS = 0 Then
  myPlane = "Select * from Baseline"
Else
  myPlane = "Select * from Baseline where ([MDS] = " & Me.cboMDS & ")"
End If

Me.Baseline_subform.Form.RecordSource = myPlane
Me.Baseline_subform.Form.Requery

Me![cboType] = Null
Me![cboClass] = Null

End Sub

Sorry if this turns out obvious, I've been cleaning up peoples excel documents all day and my eyes are crossed.

-Thanks

****EDIT****
The words that were in MDS are now calling a table called MDS and a field called MDS (Again, sorry about that. didn't do good with the naming).

 

[vbaInet]

Are you dealing with An Excel front-end/Access back-end or both Access?

By the way, why wasn't it database-y before now?

 

[TJPoorman]

First, if your criteria is text you need single quotes around your value:

"Select * from Baseline where ([MDS] = [COLOR="red"]'[/COLOR]" & Me.cboMDS & "[COLOR="Red"]'[/COLOR])"

Second, since you say it's a number instead of text, I am betting your rowsource for your combobox is set to the table rather than an actual query. In which case it is pulling the first field which is likely the ID.
You can either change your rowsource so that only the field you want is pulled, or you can change your code from this:

"Select * from Baseline where ([MDS] = '" & Me.cboMDS & "')"

To this:

"Select * from Baseline where ([MDS] = '" & Me.cboMDS.Column(1) & "')"    'Need to change the column number to the respective column

 

[pyromaniac]

Ok, fresh eyes. Lets see If I can figure this out today.

vbaInet:
I didn't know you could do an excel front access back. I knew the reverse was possible. Anyhow, it is all in access. This project is converting a bunch of excel stuff into one database where the reports make themselves.

As for database-y, by bringing the vehicle types into their own table, I force the user to pick an answer I want. For example, they choose a Miata. It could also be called a MX-5, MX5 or MX 5. This also allows me to assign priorities and weights when we make the reports.

TJPoorman:
me.cboMDS is the name of my combo box. I will try the single quotes also but this is what worked before changing to a referenced table.

about the number instead of the word, I'm not actually getting a number. I get an error. What I meant is the baseline host the MDS.ID number and not the actual vehicle name.


As I think about it, this script ran through all 1014 records and listed the MDS, then filtered out duplicates. on click, it only showed matching results. the drop down list still works but the error occurs on click. This means that the select and requery is where the issue is, right?

I'm going to give the suggestions a shot and see where I'm at.

-Thanks

 

[vbaInet]

I was a bit confused when you stated that "excel documents all day and my eyes are crossed".

Looks like TJ is on it, so I'll sit back.

 

[pyromaniac]

I'm wondering if with all the changes to the table that maybe I should rebuild the subform. I may be working in circles right now.

 

[vbaInet]

Looks like TJ isn't around yet. So basically,

1. The combo box needs to unbound
2. You will use the following code:

Private Sub cboMDS_AfterUpdate()
    Me.Baseline_subform.Form.Requery
End Sub

3. The Record Source of the subform will be:

SELECT 	*
FROM 	Baseline
WHERE 	MDS = [Forms]![FormName]![MDS] OR [Forms]![FormName]![MDS] Is Null

4. Add [Forms]![FormName]![MDS] to the Parameters list in the query selecting the appropriate data type.

 

[pyromaniac]

Ok, rebuilt the subform, Tinkered around a bit. started undoing my changes and it started working. I have no idea what fixed it, my co-worker says F'ing Magic.

 

[vbaInet]

What was explained in my last post is the way it should be done. You shouldn't need to be changing record sources.

 

[pyromaniac]

Im going to fiddle with your example at home with a cleaner DB. I have been learning Access and database stuff while building this project so there is a lot that isn't done right but works.

One thing I noticed while fiddling was that not all of the records were showing up. Over the last week the table was changed quite a bit and I was thinking that something got moved or renamed. That's why I rebuilt the subform. Maybe it was unnecessary but seems to have worked.

Anyhow, thanks for the help. I put it in my bag of reference material

 

[vbaInet]

No problemo!

Good luck with the rest!