Replicating fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, could somebody please help with my problem?

I want one field in a form to look up the text in another field and place it
in that field.

Iy you could help I would most oblidged
 
Paul,

Do you mean text boxes on a form? Forms don't have fields, they have
controls. It may depend on when you want it to happen. When you move to a
new record? When you enter a value in the first control? Are both fields on
the same form? If not, is one a sub form? If you could please provide a
little more detail, I would be happy to help.
 
Hello Klatuu,

Thank you very much for replying to my query.

Yes, I think I have the terms wrong.

What I trying to do is have a form, or do I mean table? where if I put a
name (text) in one field it would also appear in another designated field.

I am sure this is simple but I cannot find out how in my books or in the
"help" section.

Thank you very much for trying to help me.
 
Paul,

The answer depends on several variables, but let's see if we can work this
out. A form is what you put text boxes, command buttons, combo boxes(drop
downs) and other controls so you can view and manipulate your data. Usually,
a form is bound to a table or query. Tables are where the data are stored
and queries are a way of creating logical tables - not really tables, but
conceptually you can think of them as working like a table, but are really a
way of organizing and viewing your data.

Based on your post, I am assuming you are working with a form and you want
to put a value in a text box and have that value appear in another text box
on the same form.

lets say you have two text boxes txtFirstBox and txtSecondBox. In the After
Update event of txtFirstBox, select code builder and put this code:

Me.txtSecondBox = Me.txtFirstBox

If this is not what you are trying to do, let me know.
 
Hello Klatuu,

Great stuff, yes that works fine, thankyou.

If you have the time, could you help me with another problem:

I have a date field (Date1), a number field (Days) and another date field
(Date2).

I would like to put a date in "Date1" and a number in "Days". I would like
"Date2" to show the first date + the number of days entered in "Days".

Regards,

Paul
 
Hello Klatuu,

Another question i am afraid:

If I had a text box called txtThirdBox, could that lookup the text that was
created in txtSecondBox that was brought in from txtFirstBox?

I ask this because I have 8 text boxes that I would like to update, each one
copying from the previous one.

Good question, yes?

Regards,

Paul
 
Here is a function that returns the number of working days between two dates.
It will require that you have a holiday table. Mine has 2 fields, Holdate
and Holdate_Description. You can take out the code that refers to the
holiday table if you don't want to count holidays. Also, it counts every
day, for example, if you put #5/26/2005# and #5/31/2005# it will return 3.

Opps! I just reread your post. You want a date based on a date + a number
of days.
So now your have it Both ways!

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
intDayCount = 0
dtmReturnDate = DateAdd("d", -1, OriginalDate)
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + 1
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", 1, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

As to your other question, the answer is yes, but. The one thing I am
unsure of is where to put the code. The reason being that in the example I
gave previously, entering value in text box 1 updates the value in text box 2
using the After Update event. In this case, to update text box 3 from text
box 2 After Update will not work. The After Update event does not fire if
the value is changed via VBA.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function
 
Thanks, plenty for me to get my teeth into there.



Klatuu said:
Here is a function that returns the number of working days between two dates.
It will require that you have a holiday table. Mine has 2 fields, Holdate
and Holdate_Description. You can take out the code that refers to the
holiday table if you don't want to count holidays. Also, it counts every
day, for example, if you put #5/26/2005# and #5/31/2005# it will return 3.

Opps! I just reread your post. You want a date based on a date + a number
of days.
So now your have it Both ways!

Public Function AddWorkDays(OriginalDate As Date, DaysToAdd As Integer) As
Date
Dim intDayCount As Integer
Dim intNotADay As Integer
Dim dtmReturnDate As Date
intDayCount = 0
dtmReturnDate = DateAdd("d", -1, OriginalDate)
Do While True
If Weekday(OriginalDate, vbMonday) <= 5 Then
If IsNull(DLookup("[HolDate]", "Holidays", _
"[HolDate] = #" & OriginalDate & "#")) Then
intDayCount = intDayCount + 1
dtmReturnDate = OriginalDate
End If
End If
If intDayCount = DaysToAdd Then
Exit Do
End If
OriginalDate = DateAdd("d", 1, OriginalDate)
Loop
AddWorkDays = dtmReturnDate
End Function

As to your other question, the answer is yes, but. The one thing I am
unsure of is where to put the code. The reason being that in the example I
gave previously, entering value in text box 1 updates the value in text box 2
using the After Update event. In this case, to update text box 3 from text
box 2 After Update will not work. The After Update event does not fire if
the value is changed via VBA.

Function CalcWorkDays(dtmStart As Date, dtmEnd As Date) As Integer
Dim intTotalDays As Integer ' Counter for number of days
Dim dtmToday As Date ' To increment the date to compare
'Compliments of Dave Hargis

intTotalDays = DateDiff("d", dtmStart, dtmEnd) + 1 'Start with total days
'Add one to include
First Day
dtmToday = dtmStart 'Initiate compare date
Do Until dtmToday > dtmEnd
If Weekday(dtmToday, vbMonday) > 5 Then 'It is Saturday or
Sunday
intTotalDays = intTotalDays - 1 'Take one day away
for Weekend day
ElseIf Not IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmToday & "#")) Then 'It is a holiday
intTotalDays = intTotalDays - 1 'Take one day away
for the Holiday
End If
dtmToday = DateAdd("d", 1, dtmToday) 'Add a day for next
compare
Loop 'Until dtmToday > dtmEnd All days have been
compared
CalcWorkDays = intTotalDays 'Return the value
End Function


Paul said:
Hello Klatuu,

Another question i am afraid:

If I had a text box called txtThirdBox, could that lookup the text that was
created in txtSecondBox that was brought in from txtFirstBox?

I ask this because I have 8 text boxes that I would like to update, each one
copying from the previous one.

Good question, yes?

Regards,

Paul
Click to expand...
Click to expand...
 
Back
Top