Code not identifying query value correctly...

[Fish218]

So I have these two AfterUpdate events that I use to grab the counts of fish by year, collector, and assessment. These AU events automatically add 1 and the Fish Number [FishNum] autoincrements with each new record. The problem is the first new record added starts off at 0002 then the next record is 0002 then 0003 then 0004 and so on. I'm not sure why it's not correctly putting in 0001 on the first new record. I checked the query and it runs properly. Any help would be greatly appreciated.

Private Sub FishNum_AfterUpdate()
Dim varYear As Integer
Dim varAssessment
Dim varCollector As Integer
Dim varFishCount
varYear = [Forms]![frmCommercial2]![Form1]![Text788]
varAssessment = [Forms]![frmCommercial2]![Form1]![Text796]
varCollector = [Forms]![frmCommercial2]![Form1]![Combo746]
varFishCount = DLookup("[CountofIndividualFishID]", "qry2012", "[CollectionYear]=" & varYear & " And [CollectorID]= " & varCollector & " And [AssessCodeID]=" & varAssessment)
If (IsNull(varFishCount)) Then
   [Text56] = 0
   [Text58] = 1
ElseIf Not (IsNull(varFishCount)) Then
   [Text56] = varFishCount
   [Text58] = varFishCount + 1
End If
[FishNum] = varYear & "-" & [Forms]![frmCommercial2]![Form1]![Text796] & "-" & [Forms]![frmCommercial2]![Form1]![Combo746] & (Format([Text58], "0000"))
End Sub

Private Sub Length__in__AfterUpdate()
Dim varYear As Integer
Dim varAssessment
Dim varCollector As Integer
Dim varFishCount
varYear = [Forms]![frmCommercial2]![Form1]![Text788]
varAssessment = [Forms]![frmCommercial2]![Form1]![Text796]
varCollector = [Forms]![frmCommercial2]![Form1]![Combo746]
varFishCount = DLookup("[CountofIndividualFishID]", "qry2012", "[CollectionYear]=" & varYear & " And [CollectorID]=" & varCollector & " And   [AssessCodeID]=" & varAssessment)
If (IsNull(varFishCount)) Then
   [Forms]![frmCommercial2]![Form1]![subIndividualOther1]![Text56] = 0
   [Forms]![frmCommercial2]![Form1]![subIndividualOther1]![Text58] = 1
ElseIf Not (IsNull(varFishCount)) Then
   [Text56] = varFishCount
   [Text58] = varFishCount + 1
End If
[FishNum] = varYear & "-" & [Forms]![frmCommercial2]![Form1]![Text796] & "-" & [Forms]![frmCommercial2]![Form1]![Combo746] & "-" & (Format([Text58], "0000"))
[DataEntryID] = [Forms]![frmCommercial2]![Combo23]
End Sub

[June7]

You step debugged?

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[AccessMSSQL]

Is it possible for you to post the project? I can't reallly assess the issue based on the code sample.

[Fish218]

The form I'm working on is frmCommercial2. Thanks for looking.

[June7]

I tested adding first record for 2012 in subIndividualFishCisco and code had no problem creating and saving FishNum 2012-7-99-0001 into tblIndividualFish.

I find several of these Length_AfterUpdate (no Length_In_AfterUpdate) procedures so I must not have tested the correct one. Need info. Specifically which form is the error associated with?

[Fish218]

Oh yes. I apologize. The one I'm having issues with is the first "Other" tab. All the other tabs seem to work just fine. For some reason "Other" doesn't. Thanks for looking. This being my first database creation, any additional suggestions would be greatly appreciated.

[June7]

I just noticed that all those assessment subforms are feeding data to the same tables. Don't think I've ever seen a structure like this before.

Why do you have two 'Other' tabs?

Suggest you give controls meaningful names. Instead of Text758 use something like tbxDateLift. Helps a lot when analyzing code. For instance, the code kept erroring on (invalid use of null):
varCollector1 = [Forms]![frmCommercial2]![Form1]![Combo746]
So what the heck is Combo746? Had to stop code execution, go to Design view, find Combo746 and try to figure out what data needs to be entered to make code run. With a meaningful name might have just been able to go straight the control and select value.

What I find is that the combobox is for CollectorID but it doesn't offer any rows to choose from. Neither of the 'Other' tabs do.

[Fish218]

They are all feeding the same tables. That was really the only way I knew how to make the form usage as self-explanatory and simple as possible, with the fewest amount of clicks. I didn't want people to have to change numerous combo boxes when they entered data. Too many problems with people not changing the combo boxes and having the wrong data being entered into the database. I also only wanted one form. Good design or bad design?

The second 'Other' tab was a different version. I need to delete it.

The issue with CollectorID combobox must be because I deleted the names behind it. I uploaded a new version that should work. Thanks again for the help.

Perhaps I'll go back through and make valid control names when I have time.

[June7]

I found a variable reference error in event Text754_AfterUpdate behind Form1. You declare varCollectionCount1 but drop the 1 in subsequent references.

To expose that type of error during Debug>Compile, add this line to the header of all code modules: Option Explicit

I fixed that now get error in LengthInch_AfterUpdate event of form subIndividualFishOther1:
Access can't find the field 'Text56' referred to in your expression

Will let you deal with that fix.

If you want to go rename stuff, check out Rick Fisher's Find and Replace, cost about $50. Saved my butt more than once.

[Fish218]

Okay I found the problem. In tab 'Other' I have an AfterUpdate event the combo box for Species. The AU event put's the specie caught in the first line of subIndvidualFishOther1. Without that AU event the first line does not get filled. To fill subsequent lines I have the default value for [FishSpeciesID] as the combo box for species.

If I remove the AU event under species, everything works fine, but the field [Species] in first line of subIndividualFishOther1 does not fill in. Of course because the default is then set every line after the first line fills in fine. Any suggestions?

It worked on all the other tabs because I did not have the Species combo box!

[June7]

Try moving the code into the event that sets the FishNumber or the Current event of subform.

[Fish218]

So I was able to get it to work by using this AU event.

Private Sub LengthInch_AfterUpdate()
[Forms]![frmCommercial2]![Form1]![subIndividualFishOther1]![FishSpeciesID] = [Forms]![frmCommercial2]![Form1]![Combo819]
Dim varYear1 As Integer
Dim varAssessment
Dim varCollector1
Dim varFishCount
Dim txtFishCount
varYear1 = [Forms]![frmCommercial2]![Form1]![Text788]
varAssessment = [Forms]![frmCommercial2]![Form1]![Text796]
varCollector1 = [Forms]![frmCommercial2]![Form1]![Combo746]
varFishCount = DLookup("[CountofIndividualFishID]", "qry2012", "[CollectionYear]=" & varYear1 & " And [CollectorID]=" & varCollector1 & " And [AssessCodeID]=" & varAssessment)
txtFishCount = [Forms]![frmCommercial2]![Form1]![txtFishCount]
If (IsNull(varFishCount)) Then
txtFishCount = 1
[Text58] = txtFishCount
End If
If Not (IsNull(varFishCount)) Then
[Text58] = varFishCount + 1
End If
[FishNum] = varYear1 & "-" & varAssessment & "-" & varCollector1 & "-" & (Format([Text58], "0000"))
End Sub