Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-23-2016, 02:18 AM   #1
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Question Populating Textbox

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

Tor_Fey is offline   Reply With Quote
Old 05-23-2016, 02:39 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Old 05-23-2016, 02:52 AM   #3
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Populating Textbox

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

Quote:
Originally Posted by Minty View Post
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 is offline   Reply With Quote
Old 05-23-2016, 03:05 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

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                    'No need to check the rest it was null
End if

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

Me.date3 = Me.txtDate   'We got here so we must set it!
End Sub
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Old 05-23-2016, 03:13 AM   #5
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Populating Textbox

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

Quote:
Originally Posted by Minty View Post
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                    'No need to check the rest it was null
End if

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

Me.date3 = Me.txtDate   'We got here so we must set it!
End Sub
Tor_Fey is offline   Reply With Quote
Old 05-23-2016, 03:42 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

Quote:
Originally Posted by Tor_Fey View Post
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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Old 05-23-2016, 05:23 AM   #7
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Populating Textbox

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


Quote:
Originally Posted by Minty View Post
Yes wherever the txtDate is updated from.
Attached Images
File Type: png date_form.png (29.1 KB, 65 views)

Tor_Fey is offline   Reply With Quote
Old 05-23-2016, 05:44 AM   #8
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

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)
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Old 05-23-2016, 05:56 AM   #9
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Populating Textbox

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


Quote:
Originally Posted by Minty View Post
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 is offline   Reply With Quote
Old 05-23-2016, 06:10 AM   #10
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Old 05-23-2016, 06:23 AM   #11
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Populating Textbox

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

Quote:
Originally Posted by Minty View Post
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 is offline   Reply With Quote
Old 05-23-2016, 06:27 AM   #12
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Old 05-23-2016, 06:36 AM   #13
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Re: Populating Textbox

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

Quote:
Originally Posted by Minty View Post
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 is offline   Reply With Quote
Old 05-23-2016, 06:40 AM   #14
Tor_Fey
Newly Registered User
 
Join Date: Feb 2013
Location: Isle of Man
Posts: 104
Thanks: 67
Thanked 9 Times in 8 Posts
Tor_Fey is on a distinguished road
Thumbs up Re: Populating Textbox

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



Quote:
Originally Posted by Tor_Fey View Post
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
Tor_Fey is offline   Reply With Quote
Old 05-23-2016, 06:42 AM   #15
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Populating Textbox

Sorry cut and paste rush - this is what I meant;
Code:
Private Sub cmdCalDate_LostFocus()

If Not IsNull(Me.date3) Then      'First check if 3 is filled exit as by definition the others should be filled.
    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    ' If we reach here then set date3 regardless

End Sub

__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Tor_Fey (05-23-2016)
Reply

Tags
append , setfocus , textbox , vba

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Textbox not populating warriorkid1 Forms 14 08-07-2014 11:35 AM
Populating textbox from combo box gizmogeek Forms 6 04-28-2013 08:12 PM
Problem with populating a textbox avtuvy Forms 13 01-10-2010 07:24 PM
Populating textbox with value in another textbox in edit mode pinky Forms 3 07-09-2009 02:13 AM
Populating a combobox with a textbox sxi12345 Modules & VBA 2 03-19-2008 04:55 AM




All times are GMT -8. The time now is 06:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World