Populate unbound control using DLookup(?) (1 Viewer)

[adammitchell23]

Hi all,

I need to enter a value into an unbound text box on a form (frmCaptures) from a table (tblSeason).

tblSeason specifies a SeasonID (eg. "2017-2018") and the start and end dates for that season (e.g. the 2017-2018 season starts in June 1, 2017 and ends May 30, 2018).

I need users to be able to enter a single capture date in frmCaptures and then the unbound text box on the form to be populated with the SeasonID from tblSeason where the date they entered falls between the dates for that season using the AfterUpdate event).

So, if users enter a capture date as June 2, 2017, the text box populates with "2017-2018".

It seems I should be looking at using DLookup for this, but despite searching I still don't know how.

Thanks,

Ad

 

[arnelgp]

you can use a Recordset on AfterUpdate event of textbox:

Private Sub textbox_AfterUpdate()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset( _
	"SELECT tblSeason.* FROM tblSeason WHERE " & _
	"#" & Format(Me.textbox, "mm/dd/yyyy") & "# " & _
	"Between tblSeason.[season start field] AND tblSeason.[season end field]" )
With rs
	If Not (.BOF AND .EOF) Then 
		.MoveFirst
		Me!SeasonID = !SeasonIDField
		Me!OtherTextbox = !OtherFieldInTable
		...
		...
	End If
	.Close
End With
Set rs = Nothing
End Sub

if there are no [season start] and [season end] fields on tblSeason,
you can hardcode the SeasonID, but Make sure you
Format your textbox (Property->Format->Format) as Date (Short date).

Private Sub textbox_AfterUpdate()
Dim rs As DAO.Recordset
Dim strSeasonStart As String

If Format(Me.textbox, "mmdd") < "0601" Then
	strSeasonStart = Year(Me.textbox) - 1 & "-" & Year(Me.textbox)
Else
	strSeasonStart = Year(Me.textbox) & "-" & Year(Me.textbox) + 1
End if
Set rs = CurrentDb.OpenRecordset( _
	"SELECT tblSeason.* FROM tblSeason WHERE " & _
	"tblSeason.SeasonID = " & _
	Chr(34) & strStartSeason & Chr(34) )
With rs
	If Not (.BOF AND .EOF) Then 
		.MoveFirst
		Me!SeasonID = !SeasonIDField
		Me!OtherTextbox = !OtherFieldInTable
		...
		...
	End If
	.Close
End With
Set rs = Nothing
End Sub

 

[adammitchell23]

Thanks for the reply Arnel,

I'm getting a sytax error in date in query expression

Set rs = CurrentDb.OpenRecordset( _
    "SELECT tblSeason.* FROM tblSeason WHERE " & _
    "#" & Format(Me.textbox, "mm/dd/yyyy") & "# " & _
    "Between tblSeason.[startdate] AND tblSeason.[enddate]")

But I can't see it.
(My season start/end fields are [startdate] and [enddate]).

 

[arnelgp]

Replace textbox with the proper name of your textbox

 

[adammitchell23]

Yep, still no joy...

 

[isladogs]

I don't understand how this can work

Set rs = CurrentDb.OpenRecordset( _
    "SELECT tblSeason.* FROM tblSeason WHERE " & _
    "#" & Format(Me.textbox, "mm/dd/yyyy") & "# " & _
    "Between tblSeason.[startdate] AND tblSeason.[enddate]")

You are setting a date value in a textbox and saying its between a start date and an end date???? So what then?

Surely you just need a lookup - something like this (air code)
= DLookup("SeasonID","tblSeason","StartDate" <= #" & Format(Me.textbox, "mm/dd/yyyy") & "# And EndDate >=#" & Format(Me.textbox, "mm/dd/yyyy") & "#")

If I'm right, none of the recordset code is needed

 

[plog]

I see your reduction in accessing data and raise you:

Function get_Season(in_Date)
  ' takes a date, returns what season it falls in

  int_StartYear = Year(in_Date)
  ' holds first year of season, default is year of date passed

  if (Month(in_Date)<6) Then int_StartYear = int_StartYear - 1
  ' if date passed is before June, then rolls start year back 1 year

  get_Season = int_StartYear & "-" & (int_StartYear + 1) 
  ' compiles season string that shows start and end year

  End Function

If all your seasons run June 1 to May 31 (I hope you mistyped your dates and nothing special happens on May 31), then there is no need to touch any table at all, logic can sort it all out. Pass the above function a date and it can determine what season that date falls in.

 

[arnelgp]

It works believe me, give it a test.

 

[isladogs]

Arnel
All that is needed is to pull the academic year e.g. 2017-18 from a table or determine it using a function
Even if your code works, it seems a very long-winded way of getting the answer.
In my view, never use a recordset when its not needed

plog
In my own school apps I have 2 functions remarkably similar to yours to do the same thing - the year is 1 Sept - 31 Aug but the principle is the same

The first just returns a value like 2017

Function GetAcYear(myDate)
    If Month(myDate) >= 9 Then
        GetAcYear = Format(myDate, "yyyy")
    Else
        GetAcYear = Format(DateAdd("yyyy", -1, myDate), "yyyy")
    End If

End Function

The second returns e.g. 2017-18
I wrote it many years ago as you'll see from the special cases needed

Function GetFullAcYear(myDate)
    If Mid(GetAcYear(myDate), 3, 1) = 0 Then 'e.g. 2008
        If Right(GetAcYear(myDate), 1) <> 9 Then 'e.g. 2008 => 2008-9
            GetFullAcYear = GetAcYear(myDate) & "-" & Right(GetAcYear(myDate), 1) + 1
        Else 'e.g. 2008 => 2008-9
            GetFullAcYear = GetAcYear(myDate) & "-" & Right(GetAcYear(myDate), 2) + 1
        End If
    Else 'e.g. 2010 => 2010-2011
        GetFullAcYear = GetAcYear(myDate) & "-" & Right(GetAcYear(myDate), 2) + 1
    End If
End Function

Do you want to raise further?

 

[plog]

Seems my initial raise was sufficient. Your new code adds nothing to the current conversation.

 

[isladogs]

Oooh .. Handbags at dawn is it?

My answer was a lengthy way of saying I agree!

 

[arnelgp]

i don't understand What is not working
for you. Is your startdate and enddate
date type?
did you format the unbound textbox as Date?
show us the structure of your table
instead of us throwing wild guesses.

 

[isladogs]

4 ways to skin a cat ....
The attached db contains all 4 methods described so far by arnel, plog & myself.
All methods work
Take your choice

 

[adammitchell23]

OK, sorry to cause arguments. It may be that's I've explained myself poorly, for which I apologise. I've attached an example database to clarify.

In frmCaptures, the user enters a date in txtCaptureDate.
txtSeason should then display the relevant [SeasonID] (from tblSeason) by checking if the date entered in txtCaptureDate is between the [StartDate] and [EndDate] in tblSeason.

The season (eg. "2017-2018") which then displays in txtSeason doesn't have to do anything except sit there and look pretty.

In this case, the seasons are October-October not June-June (but this will vary in different versions of the form.

Thanks everyone!

 

[arnelgp]

i used DLookup instead.
see txtCapture's OnLostFocus event.

 

[isladogs]

OK, sorry to cause arguments. It may be that's I've explained myself poorly, for which I apologise.

I don't think you caused a real argument
You were given 4 different solutions by arnel, plog & myself.
All of them work
I put all 4 of those into the '4 Ways' database for you to choose from

Other than you changing the 'changeover month' from your original June to October, you just needed to use whichever you preferred

 

[adammitchell23]

Perfect! Thanks all of you for your help; DLookup was a winner after all!

Ad

 

[isladogs]

Its certainly the simplest method of the 4 ways to skin that poor cat ...

However using a function is best if you need to reuse the code elsewhere in your database

Anyway its working which is the main thing