Button to combox to table interaction issues.

[Slashback115]

I am working on a project (for fun) and am almost complete but stumped on this last little part. Any help would be greatly appreciated.

This is for Access 2013

I have a basic Excel table with names. Artname, Dwarven, Elvish, Human.

Button (artbutton)
ComboBox (selectname)
Text Box (arttext)
Table (Gen)
Query (Genq)

I need to have the combo box select 1 of the many columns. Once selected the button (on_click) will randomize all the values in that column and display one in the text field.

I have it working to the point of selecting the first field Artname and randomizing it. But no matter what I try, I cannot get the button to recognize the combobox selection.

Here is the code I currently have. Thanks again!

(This works for the 1 random column.)
----------------------------------------------------------------------------------------------------------

Private Sub artbutton_Click()
Dim MinID As Integer
Dim MaxID As Integer
Dim RandomVal As Integer
Dim arttext As String


MinID = DMin("ID", "Gen")
MaxID = DMax("ID", "Gen")
Randomize
RandomVal = Int((MaxID * Rnd) + MinID)
arttext = getRandomStuff(RandomVal)
Me.arttext.Value = arttext


End Sub

Public Function getRandomStuff(lookupVal As Integer) As String

Dim rst As DAO.Recordset
Dim sqlStr As String

sqlStr = "Select Gen.Artname FROM Gen Where Gen.ID = " & lookupVal & ""
Set rst = CurrentDb.OpenRecordset(sqlStr)
getRandomStuff = rst!Artname
rst.Close
Set rst = Nothing



End Function


----------------------------------------------------------------------------------------------------------





This was my attempt getting it to work with multiple columns.
----------------------------------------------------------------------------------------------------------
Private Sub artbutton_Combo(Rannum)
Dim MinID As Integer
Dim MaxID As Integer
Dim RandomVal As Integer
Dim arttext As String


MinID = DMin("ID", "Gen")
MaxID = DMax("ID", "Gen")

If Ranname = "Artname" Then MinID = 2 & MaxID = 2047
If Ranname = "Dwarven" Then MinID = 2 & MaxID = 1231
If Ranname = "Drow M" Then MinID = 2 & MaxID = 1907
If Ranname = "High Elf" Then MinID = 2 & MaxID = 838
If Ranname = "Eastern" Then MinID = 2 & MaxID = 1225
If Ranname = "Orcish" Then MinID = 2 & MaxID = 1747
If Ranname = "Gnomish" Then MinID = 2 & MaxID = 933
If Ranname = "Roman" Then MinID = 2 & MaxID = 847
If Ranname = "Common Male" Then MinID = 2 & MaxID = 318
If Ranname = "Common Female" Then MinID = 2 & MaxID = 345
If Ranname = "Location" Then MinID = 2 & MaxID = 959

Randomize
RandomVal = Int((MaxID * Rnd) + MinID)
arttext = getRandomStuff(RandomVal)
Me.arttext.Value = arttext


End Sub

Public Function getRandomStuff(lookupVal As Integer) As String

Dim rst As DAO.Recordset
Dim sqlStr As String

sqlStr = "Select Gen.Artname FROM Gen Where Gen.ID = " & lookupVal & ""
Set rst = CurrentDb.OpenRecordset(sqlStr)
getRandomStuff = rst!Artname
rst.Close
Set rst = Nothing

End Function

Private Sub selectname_OnChange()
Dim Ranname As String

Ranname = selectname.Selected
artbutton_Combo Ranname

End Sub
----------------------------------------------------------------------------------------------------------

 

[plog]

That post could have been a lot more concise. I looked at your image an worked on this premise:

You want a user to select a field, and then populate a box on your field with a randomly chosen value from that field in your Gen table.

If that's correct, you can do that with one line of code:

DLookUp("[SelectedFieldHere]","Gen","[ID]=" & Int((DMax("[ID]","Gen")-DMin("[ID]","Gen")+1)*Rnd()+DMin("[ID]","Gen")))

You would need to replace "[SelectedFieldHere]" with the value they choose. And your Gen table cannot be missing any ID values.

 

[Slashback115]

Thanks for the reply, so that goes on the Artbutton?

 

[plog]

That would be the meat of your code that triggers when they click the button. I don't know what you have on there now, but it looks super-over engineered.

 

[Slashback115]

I'm confused though. You say select field, but it is all the fields in the table not just 1 column that I need. How would I put that in?

 

[plog]

I thought the user chooses a field, then you randomly choose a value from that field to display?

 

[Slashback115]

Yes, that is correct. But I keep getting expression errors, it cannot find my expression [SelectedFieldHere] because if I choose a column name like "Dwarven" it only shows Dwarven.

 

[Slashback115]

So DLookUp("[Dwarven]","Gen","etc... This will generate Dwarven Names
DLookUp("[ID]","Gen","etc... This will generate ID numbers.

How do I get [?] to = selectedname (combobox) value?

 

[plog]

Yes, you need to grab the combo box value from the form, put it into a variable and then use the variable in the DLookup.

Your initial code is able to reference inputs on the form. Look there for a hint.

 

[Slashback115]

So my combobox is called Selectname. (i am sure this is wrong I am really new to this)

----------------------------------------------------
Private Sub selectname_Change()

Dim selectname As String

selectname = Me!cboselectname
Me(selectname).RowSource = ""


End Sub
----------------------------------------------------

Then your code would be arttext = DLookup("[selectname]", "Gen", "[ID]=" & Int((DMax("[ID]", "Gen") - DMin("[ID]", "Gen") + 1) * Rnd() + DMin("[ID]", "Gen"))) ?

 

[plog]

If your combobox is named Selectname, then you would reference its value like so:

Me.Selectname.value

In that big dlookup I gave you, you would place it's value like so:

DLookup("[" & Me.Selectname.value & "]", "Gen", "[ID]=...

 

[Slashback115]

Getting invalid use of .Value. Maybe my combo box is setup wrong in Access? Would you be willing to take the accb file and tweak it real fast? I know it is asking alot.

 

[plog]

Sure, post it in this thread. Use the 'Go Advanced' button.

 

[Slashback115]

Even after compressing and zipping it is 3.5mb Have an email?

 

[plog]

Make a new database. I only need the form and the Gen table.

 

[Slashback115]

It is still over 2... Not sure how.

I will put a random email on here that you can email so you don't give it out on the forms?

I dont know why it is so big.

 

[Slashback115]

So good news, It started working...

If I select a value in the combo box I get an error. Invalid use of Property Selectname.Value

However if I press the button, it will generate a random name from the field selected.

Almost there! Any ideas?

My Combo box uses

Rowsource Type : Value list
Rowsource is: Artname;Dwarven;etc...

 

[plog]

Nope, sorry. Can you send yur database? I sent a private message with my email.

 

[Slashback115]

Sent, thanks!

 

[plog]

DLookup cannot return a Null value--you have lots of Null values in your table. That's the cause of the error. To route around it use this code:

Private Sub artbutton_Click()
 ' loads random value from selected field in arttext combobox
 
str_Display = ""                ' will hold what to display in box

str_Criteria = "[ID]=" & Int((DMax("[ID]", "Gen") - DMin("[ID]", "Gen") + 1) * Rnd() + DMin("[ID]", "Gen"))
   ' criteria to use for DLookup and DCount

If DCount("[" & Me.selectname.Value & "]", "Gen", str_Criteria) Then str_Display = DLookup("[" & Me.selectname.Value & "]", "Gen", str_Criteria)
    ' makes sure value exists for randomly chosen record, if so, uses it

Me.arttext.Value = str_Display

End Sub

The above code will show nothing when it encounters a Null value.