Setting default values in combo box to match the value of previous record

[CraigDolphin]

G'day. I've been browsing the forum trying to find a solution to a problem, without much luck, and am hoping that someone might be kind enough to help me more directly.

I'm a biologist and a newbie to access: with the distinction of being completely clueless about VBA. Nonetheless, I have been tasked with creating a database for storing data obtained from biological surveys of juvenile salmon and harvest/spawner surveys of adult salmon.

The bones of the db are in place and functional. The problem I have is streamlining the data entry process to minimise keystrokes/mouse clicks.

I have a subform for entering fish records with attributes such as 'species_name', 'fork_length', 'count', 'presence of tags, etc. The idea is that fish can be entered as individuals (count=1) or groups (count>1) with attribute data at the appropriate level (eg, fork lengths only entered for individuals. We typically measure the fork lengths of the first 20 individuals of each species and then tally the remainder).

One problem I face is that fish often occur in schools, and it becomes quite tiresome to click and select the same species_name combo box value for each record when entering multiple individuals of fish of the same species.

What I would like to do is set the default value for that combo box (on the fish subform) to match the value entered for the previous record.

I suspect that you could use the after update trigger to execute some code to change the default value each time something is entered manually into the combo box. Unfortunately, I know nothing about how to write the appropriate code.

Can anyone offer me some guidance?

Thanks in anticipation.
Sincerely,
Craig Dolphin

 

[llkhoutx]

On the AfterUpdate event of the combobox, store the bound column value of the combobox into the combobox tag property, eg

Private Sub cbo_AfterUpdate()
'this event fires are when the ccombobox updates
me!cbo.tag = me!cbo
end sub


On the instant form's Oncurrent event (a new record) store the combobox tag property value into the combobox, eg

Private Sub Form_Current()
'this event fires on a new record
'don't overwrite existing values
if me!cbo & "" = "" then
me!cbo = me!cbo.tag
end if
end sub

Note that initially there will be no value. The combobox should not have a Default Property value and the underlying table should not have a default value for the instant field.

 

[CraigDolphin]

Ilkhoutx,

Brilliant! Thanks very much. Worked like a charm.

So am I right in 'interpreting' the code this way:

After update....take the value entered and assign it to some variable called 'tag'.

On current (new record), if combo value is blank then assign the value of the tag variable to the combo box value.

If this is correct, is the 'tag' variable something created by this code, or was it pre-existing and we're just 'using' it for this purpose?

Not that it makes any difference, I just want to learn in case I come across something like this in the future. In any case, thanks again for taking the time to help. It's much appreciated.

Sincerely,
Craig Dolphin

 

[llkhoutx]

After update....take the value entered and assign it to some variable called 'tag'.

No, store the value in the combo box's tag property.

On current (new record), if combo value is blank then assign the value of the tag variable to the combo box value.

On current (new record), the value in the combo box tag property is stored in the combo box, only if the combo box vlae is empty or Null. This allows you to cycle through the records, without changing an existing value.

If this is correct, is the 'tag' variable something created by this code, or was it pre-existing and we're just 'using' it for this purpose?

The tag property exists for every form object. You merely use it to save the last value "input" is the combo box. Right click on any object on a form open in desihn view. A list of that objects orperties pos up. It's a long list and properties vary slightly with object type. There's also a tool bar icon for displaying properties of a selected object. These properties are quite frequerntly modified on the fly. We're merely usiong the property for this purpose. It can also include multiple values; tedious code exists for the retrival on a certain value from a control tag property based upon some condition or occurrence.

Your a lucky guy to be counting fins! I'm love trout fishing. Have only seen salmon in the super maker and climbing the fish ladder at some museum in Seattle. What fun. Had I only known that world existed when I was young . . .

 

[CraigDolphin]

Thanks for the explanation Ilkhoutx

And yes, working with critters is a nice way to make a living....shame there's no money in it!

 

[nateobot]

Your a lucky guy to be counting fins! I'm love trout fishing. Have only seen salmon in the super maker and climbing the fish ladder at some museum in Seattle. What fun. Had I only known that world existed when I was young . . .

OT: Take a fishing trip to Algoma, Wisconsin sometime. I went with my Father-in-Law last year. Cost us $400 each for 3 days King Salmon fishing on Lake Michigan with a Capt and First Mate. Had a blast and caught enough salmon to last us all year (came back with 20 fish each averaging 15 pounds).

 

[llkhoutx]

Happy Holidays

 

[llkhoutx]

Forgot to add, "Ahhhhhhhhh but the salmon!"

Do what you love and the money will follow.

 

[CraigDolphin]

Hi again, hope you had a good holiday

I have discovered some unintended consequences to implementing the code and was wondering if you might suggest a workaround to let me eat my cake and keep it too

Sometimes when you do a beach seine you catch nothing at all. Previously, I could enter the set information (main form), conditions (separate subform of the main form), and then skip entering anything into the catch subform at all. However, now it seems that if I don't enter anything in the catch subform the form automatically enters a record with the last-used species name. Obviously, this risks entering false information into the db.

Similarly, if I accidentally click the forward arrow on the catch subform navigation bar it will enter a record with the previously-used species name. If I 'delete' the extra record it simply replaces it with a new record instantaneously.

Now, I know that it is simply doing what I asked for in this second instance, but I was hoping it would behave more like a default value such that no record would actually be entered unless I click elsewhere in the subform, or else click the forward nav button again.

I tried placing the code in different events to see if I could fix it but to no avail.

Any thoughts on how to achieve this?

Oh, and in a separate glitch, I modified a code for the Notinlist event for the Species name combo box that prompts the user to add new species names to a table that supplies the names for the combo box's drop down menu. For some reason, this causes the 'repeat species name code' to freeze such that it always inputs the species name 'Chinook' (top value in the drop down list) rather than echoing the last entry. Is there some reason why the code in the notinlist event would interfere with the code above?

I'll add the notinlist code below in case you want to peruse it. I can't see any reference to the .tag property in the code so I'm puzzled why it would conflict.

Thanks again for any help you might provide.

Cheers,
Craig


Private Sub Species_NotInList(NewData As String, Response As Integer)
On Error GoTo Species_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The species " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Lummi Natural Resources")
If intAnswer = vbYes Then
strSQL = "INSERT INTO Species_List([Species_Name]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new species has been added to the list." _
, vbInformation, "Lummi Natural Resources"
Response = acDataErrAdded
Else
MsgBox "Please choose a species from the list." _
, vbInformation, "Lummi Natural Resources"
Response = acDataErrContinue
End If
Species_NotInList_Exit:
Exit Sub
Species_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume Species_NotInList_Exit

End Sub

 

[llkhoutx]

Am pulling an all nighter here getting a demo out. Ugh! Holidays were great. My oldest son has been here for Christmas and is leaving this AM.

As for your data being carried forward. This should be done by default if you really intend that. Obviously you do. Put your carry forward code behind a Button and stuff it in the new record on the button On_Click event. Although, I save data to be carried forward in a field's tag property, it's never the default condition for new records. You have simply been felled by the default trap.

I don't see an error in the NotInList code. However, I've enclosed some NotInList code that I use where I also pass the table name as well as the newdata. I use it hundreds of time with different table names in a big program.

Public Sub NotInListX(stbName As String, NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
If vbNo = MsgBox("'" & NewData & "'" & vbCr & vbCr & _
"is not in the list." & vbCr & vbCr & _
"Do you want to add it?", vbYesNo + vbDefaultButton2, "basNotInList - sub NotInListX") Then
Response = acDataErrDisplay
Exit Sub
End If

Set db = CurrentDb
Set rs = db.OpenRecordset(stbName, dbOpenDynaset)
rs.AddNew
Dim lngX As Long
'This function builds seeded random indicies
lngX = fcnIncIndex(stbName)
rs.Fields(0) = lngX ' fcnIncIndex(stbName)
rs.Fields(1) = NewData
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Response = acDataErrAdded
End Sub

I assume that table Species_List has an autonumber index. If it doesn't, that may be your problem. Your may be creating a new record without an index. Maybe the Species combo box has a default value.

I encountered the "top of the list" problem before, but can't recall where. Alzheimer's, you know.

Put a breakpoint in the NotInList code and check the table after your SQL statement is executed. That may indicate what's going on.

The "DoCmd.SetWarnings False" in your code probably shouldn't be there.

The NotInList issue should not be relevant to your subform default issue.

Regards

 

[CraigDolphin]

Ilkhoutx, if you're ever up in north western WA I owe you a beer or two!

Using the command button to navigate records and run the carry forward value code worked to resolve all my problems (well, code related ones at least...). And yes, the Species_list table does have an autonumber primary field. I also have a field called rank which I use to set the order of the items in the drop down list. I have set up the table such that new items get a default rank of 100 so that they appear at the bottom of the drop down menu since they are likely to be rare species (the most commonly encountered are already in the list).

I tried using your notinlist code but I must have messed something up because I kept getting an error message. However, the original code I had works just fine now that I've gone to using command buttons to add new records etc.

I noticed your code was a Public sub whereas the one I used is a Private sub...Perhaps I was using it in the wrong place....is there a difference between these designations that I need to be aware of?

Thanks again!

 

[llkhoutx]

I sent my code, just as an example; using a recordset to update, rather r=that sql.

It's public because I use it for every NotInList event, not just one. That's what the underlying table is also an argument.

In addition to the argument change the function is NotInListX, emphasis on the X. A controls, NotInList event calls my function. That way I didn't have to replicate the NotInList code a multitude of times for every combobox.

Rank is obviously not getting set when you add a species, so that's probably why you're getting what you get. You can also add that (rank) in your NotInList function if you know the new value and easily if you use recordsets as opposed to sql. At least it's easier for me.

Regards