Populating Textbox (1 Viewer)

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Good Morning All;

I have a field on a bound form called ‘date’; that has a date picker associated to it; this populates an unbound textbox once the date has been selected.
What I need to do is; once the date has populated the unbound textbox; is to populate a bound textbox field on my form (date1), but if this field contains data then move to the second bound textbox field (date2) and move to (date3) if ‘date2’ contains data.

I the following code on a combobox that updates textboxes when selected, but I cannot get the same code to work for the scenario above:

Private Sub checktype_AfterUpdate()
Dim intCheckName As Integer

intCheckName = 1

If Nz(Me.check_name1, 0) = 0 Then 'apply selected name to check_Name1
intCheckName = 1
ElseIf Nz(Me.check_name2, 0) = 0 Then 'apply selected name to check_Name2
intCheckName = 2
ElseIf Nz(Me.check_name3, 0) = 0 Then 'apply selected name to check_Name3
intCheckName = 3
Else 'all 3 check_Names contain a value
MsgBox "All check names selected", vbInformation
Exit Sub
End If

Select Case intCheckName 'check preceding check_Name fields do not equal current name
Case 1
Me.check_name1 = Me.checktype
Case 2
If Me.checktype = Me.check_name1 Then 'matches Me.check_Name1
MsgBox "The name " & Me.checktype & " has already been submitted", vbInformation
Exit Sub
End If
Me.check_name2 = Me.checktype
Case 3
If Me.checktype = Me.check_name1 Or Me.checktype = Me.check_name2 Then
MsgBox "The name " & Me.checktype & " has already been submitted", vbInformation
Exit Sub
End If
Me.check_name3 = Me.checktype
End Select
End Sub

If anyone could help with this, it would greatly be appreciated.

Kind Regards
Tor Fey
 

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
I suspect calling the field 'Date' is causing you some issues, as it is a reserved word. Try changing it and post up the code that isn't working - the working code is not helping us identify your problem.

I also suspect that you could do your working code just in the select case statement without the first Nz(etc etc ) bits.
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

The actual date field isn't called 'Date'; it is just simply called: date, I put the quote marks to highlight my field name to this forum. apologies for the confusion.

so my main field that gets populated from the date picker is called: Date
and the other bound fields are called: date1, date2 & date3

So I don't know; how I can write code to populate the fields date1, date2 & date3; in turn after the date has been selected. So if date1 isnul then populate with date, but if date1 contains a value then move to date2 etc.

Kind Regards
Tor Fey

I suspect calling the field 'Date' is causing you some issues, as it is a reserved word. Try changing it and post up the code that isn't working - the working code is not helping us identify your problem.

I also suspect that you could do your working code just in the select case statement without the first Nz(etc etc ) bits.
 

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
My advice was made assuming that your field was called Date - Change the unbound field name to something else try txtDate . Then you code would be something like;
Code:
If IsNull(Me.date1) Then 
    Me.date1 = Me.txtdate
    Exit Sub                    [COLOR="Green"]'No need to check the rest it was null[/COLOR]
End if

If IsNull(Me.date2) Then 
    Me.date2 = Me.txtdate
    Exit Sub                   '[COLOR="Green"]No need to continue checking it was null[/COLOR]
End if

Me.date3 = Me.txtDate   [COLOR="green"]'We got here so we must set it![/COLOR]
End Sub
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

Thanks for this, Is this going to be code as an afterupdate event on the filed: txtDate? or if not how do I code it?

Regards
Tor Fey

My advice was made assuming that your field was called Date - Change the unbound field name to something else try txtDate . Then you code would be something like;
Code:
If IsNull(Me.date1) Then 
    Me.date1 = Me.txtdate
    Exit Sub                    [COLOR=green]'No need to check the rest it was null[/COLOR]
End if

If IsNull(Me.date2) Then 
    Me.date2 = Me.txtdate
    Exit Sub                   '[COLOR=green]No need to continue checking it was null[/COLOR]
End if

Me.date3 = Me.txtDate   [COLOR=green]'We got here so we must set it![/COLOR]
End Sub
 

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
Hi Minty;

Thanks for this, Is this going to be code as an afterupdate event on the filed: txtDate? or if not how do I code it?

Regards
Tor Fey

Yes wherever the txtDate is updated from.
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

I can't add the after update event to the field that populates txtDate as there is no afterupdate available, please see attached screenshot.

Would this then be set as an 'onchange' event?

Regards
Tor Fey


Yes wherever the txtDate is updated from.
 

Attachments

  • date_form.png
    date_form.png
    29.1 KB · Views: 102

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
Okay that's interesting. You are showing me the event properties for the command button. Why not use the inbuilt date picker and set the format to short date on your txtDate text box control? Which does have an after update property.

(Assuming you are using Acc 2007 onwards)
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

I am using access 2003 sp2, and the only way to create a date picker with this version of access, is as I have done.

I don't have the option to upgrade access; as my work will not allow it to happen, but we use access for almost everything, this is why I have to do things in a funny way :(

Regards
Tor Fey


Okay that's interesting. You are showing me the event properties for the command button. Why not use the inbuilt date picker and set the format to short date on your txtDate text box control? Which does have an after update property.

(Assuming you are using Acc 2007 onwards)
 

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
Ah okay that makes things a little more interesting.
You need to attach the update code somewhere, where does the focus end up after you have used the date picker button? I would maybe put the code into the lost focus event of that or even another command button, both are not ideal though.
The other solution would be to modify the date picker code - but I suspect that would mess it up everywhere else it was used, unless you got a bit clever with your modifications.
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

The 'Lost Focus' event, works nicely, thanks so much for that, it hadn't occurred to me to put the code there :)

Just need to sort out some validation now, for when my 3 date fields boxes are all full so that the user can't enter any further data.

going to use a msgbox for this I think, thanks for your help with this, it has truly wrecked my head :)

Kind Regards
Tor Fey

Ah okay that makes things a little more interesting.
You need to attach the update code somewhere, where does the focus end up after you have used the date picker button? I would maybe put the code into the lost focus event of that or even another command button, both are not ideal though.
The other solution would be to modify the date picker code - but I suspect that would mess it up everywhere else it was used, unless you got a bit clever with your modifications.
 

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
For validation put at the beginning of the code something like;

If Not IsNull(Me.Date3) Then
MsgBox "Dates already set!"
Exit Sub
End If

Then it won't run the code to do the updates.
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

so I have tried this code:

Private Sub cmdCalDate_LostFocus()

If IsNull(Me.date1) Then
Me.date1 = Me.txtdate
Exit Sub
End If

If IsNull(Me.date2) Then
Me.date2 = Me.txtdate
Exit Sub
End If

Me.date3 = Me.txtdate
End If

If Not IsNull(date3) Then
MsgBox "All Date Fields Have A Date, You Can Not Enter Any Further Updates!"
Exit Sub
End If

End Sub

But it seems to error, I think it could be the 'End if' after the statement: Me.date3 = Me.txtdate

Should this be 'Else' instead of 'End if'?

Regards
Tor Fey

For validation put at the beginning of the code something like;

If Not IsNull(Me.Date3) Then
MsgBox "Dates already set!"
Exit Sub
End If

Then it won't run the code to do the updates.
 

Tor_Fey

Registered User.
Local time
Today, 13:33
Joined
Feb 8, 2013
Messages
121
Hi Minty;

Never mind; I have sorted it; I didn't read your part of putting the code at the beginning, everything now works perfectly, so once again thanks so much for your help, very much appreciated :)

Kind Regards
Tor Fey



Hi Minty;

so I have tried this code:

Private Sub cmdCalDate_LostFocus()

If IsNull(Me.date1) Then
Me.date1 = Me.txtdate
Exit Sub
End If

If IsNull(Me.date2) Then
Me.date2 = Me.txtdate
Exit Sub
End If

Me.date3 = Me.txtdate
End If

If Not IsNull(date3) Then
MsgBox "All Date Fields Have A Date, You Can Not Enter Any Further Updates!"
Exit Sub
End If

End Sub

But it seems to error, I think it could be the 'End if' after the statement: Me.date3 = Me.txtdate

Should this be 'Else' instead of 'End if'?

Regards
Tor Fey
 

Minty

AWF VIP
Local time
Today, 13:33
Joined
Jul 26, 2013
Messages
10,354
Sorry cut and paste rush - this is what I meant;
Code:
Private Sub cmdCalDate_LostFocus()

If Not IsNull(Me.date3) Then      [COLOR="Green"]'First check if 3 is filled exit as by definition the others should be filled.[/COLOR]
    MsgBox "All Date Fields Have A Date, You Can Not Enter Any Further Updates!"
    Exit Sub
End If

If IsNull(Me.date1) Then
    Me.date1 = Me.txtdate
    Exit Sub
End If

If IsNull(Me.date2) Then
    Me.date2 = Me.txtdate
    Exit Sub
End If

Me.date3 = Me.txtdate  [COLOR="Green"]  ' If we reach here then set date3 regardless[/COLOR]

End Sub
 

Users who are viewing this thread

Top Bottom