Copying a field to the next available blank field on another form?

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

Guest

I have a series of lists of addresses on a form that I want to transfer to
another form when the user clicks on an address. Eventually creating a new
correct list.

The target address list must ultimately be in the correct order; the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on the left
will be the Target Form, and the form on the right is the Selector Form. Both
Forms are continuous forms because each list of addresses are different
lenghts.

The twist is that the Selector form addresses will be in a random order, and
by the user clicking on a selection, that address will be transferred to the
Target Form list. Eventually all available selections will be chosen, making
a complete list.

The problem is that I can’t figure out how to ensure that the selected
address goes into the next available blank target field on the target form,
and doesn’t fill in an already populated text box.

Does anyone know how I would do this with code?
 
Correct order? Does sorting not do this for you without the need for all
this work for the user?

<confused>
 
The key word is my post was 'challenge', sorry I should have made it much
clearer, this is a requirement for a user test.

meanhwile, I have made an attempt at using this code:

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.[frm_Run_Test].[Waypoint_Combo]) Then
Forms.[frm_Run_Test].[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But I get this error message when run it:

Object doesn't support this object or property MS Access

I wish Access had better error handling than this; for example, what object
it it reffering to?, and why?
 
I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is there a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub
 
You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though, haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the other form
is based on, then requery or reopen the second form

--
hth,
SusanV

efandango said:
I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

SusanV said:
Correct order? Does sorting not do this for you without the need for all
this work for the user?

<confused>
Click to expand...
Click to expand...
 
Susan, thanks for the advice. I'm afraid using SQL insert in VBA is a little
beyond my skills, though i'm sure it's a more elegant soltion. So for now,
i'll have to rely on 'Action' stlye coding. I'll try your 'DoCmd...'
suggestion and let you know how i get on...


SusanV said:
You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though, haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the other form
is based on, then requery or reopen the second form

--
hth,
SusanV

efandango said:
I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

SusanV said:
Correct order? Does sorting not do this for you without the need for all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to transfer
to
another form when the user clicks on an address. Eventually creating a
new
correct list.

The target address list must ultimately be in the correct order; the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on the
left
will be the Target Form, and the form on the right is the Selector
Form.
Both
Forms are continuous forms because each list of addresses are different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be transferred
to
the
Target Form list. Eventually all available selections will be chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the selected
address goes into the next available blank target field on the target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
Hello Susan,

I tried this:

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNewRecord
Else
MsgBox "All fields are filled"
End If


End Sub


But it didn't work, it simple went to the:

MsgBox "All fields are filled"


SusanV said:
You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though, haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the other form
is based on, then requery or reopen the second form

--
hth,
SusanV

efandango said:
I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

SusanV said:
Correct order? Does sorting not do this for you without the need for all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to transfer
to
another form when the user clicks on an address. Eventually creating a
new
correct list.

The target address list must ultimately be in the correct order; the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on the
left
will be the Target Form, and the form on the right is the Selector
Form.
Both
Forms are continuous forms because each list of addresses are different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be transferred
to
the
Target Form list. Eventually all available selections will be chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the selected
address goes into the next available blank target field on the target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
It's actually probably simpler, but perhaps this will work better for you.

;-D

efandango said:
Susan, thanks for the advice. I'm afraid using SQL insert in VBA is a
little
beyond my skills, though i'm sure it's a more elegant soltion. So for now,
i'll have to rely on 'Action' stlye coding. I'll try your 'DoCmd...'
suggestion and let you know how i get on...


SusanV said:
You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though,
haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the other
form
is based on, then requery or reopen the second form

--
hth,
SusanV

efandango said:
I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is
there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

:

Correct order? Does sorting not do this for you without the need for
all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to
transfer
to
another form when the user clicks on an address. Eventually creating
a
new
correct list.

The target address list must ultimately be in the correct order; the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on
the
left
will be the Target Form, and the form on the right is the Selector
Form.
Both
Forms are continuous forms because each list of addresses are
different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be
transferred
to
the
Target Form list. Eventually all available selections will be
chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the
selected
address goes into the next available blank target field on the
target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
I was afraid of that. You can use SQ pretty easily though. As I understand
your code, the data you want to grab is in the control Me.Waypoint_Selector.
I don't know the name of the table that the second form is based on, so I'll
call it "tblTEMP", and the field "Waypoint." Give this a try (modifying as
needed):

Private Sub Waypoint_Selector_Click()

Dim strWaypoint as string

strWaypoint = Me.Waypoint_Selector

If IsNull(Forms.[frm_Run_Test].[Waypoint_Combo]) Then

DoCmd.RunSQL "INSERT INTO tblTEMP (Waypoint) VALUES " & strWaypoint &
"'"
DoCmd.OpenForm "Your2ndForm"


Else
MsgBox "All fields are filled"
End If

End Sub

efandango said:
Hello Susan,

I tried this:

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNewRecord
Else
MsgBox "All fields are filled"
End If


End Sub


But it didn't work, it simple went to the:

MsgBox "All fields are filled"


SusanV said:
You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though,
haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the other
form
is based on, then requery or reopen the second form

--
hth,
SusanV

efandango said:
I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is
there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

:

Correct order? Does sorting not do this for you without the need for
all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to
transfer
to
another form when the user clicks on an address. Eventually creating
a
new
correct list.

The target address list must ultimately be in the correct order; the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on
the
left
will be the Target Form, and the form on the right is the Selector
Form.
Both
Forms are continuous forms because each list of addresses are
different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be
transferred
to
the
Target Form list. Eventually all available selections will be
chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the
selected
address goes into the next available blank target field on the
target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
Susan,

It has just occured to me that you may have been slightly misled, i'm
referring to acNewRecord, the thing is, the target subform already has the
records, except the missing address field data that i want to get from the
Selector Form. Therefore I'm assuming that your original code should have
read:

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNext

Will your SQL suggestion still work, or can we still utilise the 'DoCmd...'
albeit with the significant acNext code?.


MY Main Form is called: Runs
My Target Subform is Called: frm_Run_Test
My Target Table is Called: tbl_Run_Waypoints_Test
My Target Control is Called: AnswerWaypoint

I feel like i am nearly there, and appreciate your help and patience on this

regards

Eric
SusanV said:
I was afraid of that. You can use SQ pretty easily though. As I understand
your code, the data you want to grab is in the control Me.Waypoint_Selector.
I don't know the name of the table that the second form is based on, so I'll
call it "tblTEMP", and the field "Waypoint." Give this a try (modifying as
needed):

Private Sub Waypoint_Selector_Click()

Dim strWaypoint as string

strWaypoint = Me.Waypoint_Selector

If IsNull(Forms.[frm_Run_Test].[Waypoint_Combo]) Then

DoCmd.RunSQL "INSERT INTO tblTEMP (Waypoint) VALUES " & strWaypoint &
"'"
DoCmd.OpenForm "Your2ndForm"


Else
MsgBox "All fields are filled"
End If

End Sub

efandango said:
Hello Susan,

I tried this:

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNewRecord
Else
MsgBox "All fields are filled"
End If


End Sub


But it didn't work, it simple went to the:

MsgBox "All fields are filled"


SusanV said:
You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though,
haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the other
form
is based on, then requery or reopen the second form

--
hth,
SusanV

I figured out what the problem was, i didn't refer to the 'main' form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next available
blank field; Is it because the target is a continous form?, if so is
there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

:

Correct order? Does sorting not do this for you without the need for
all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to
transfer
to
another form when the user clicks on an address. Eventually creating
a
new
correct list.

The target address list must ultimately be in the correct order; the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on
the
left
will be the Target Form, and the form on the right is the Selector
Form.
Both
Forms are continuous forms because each list of addresses are
different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be
transferred
to
the
Target Form list. Eventually all available selections will be
chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the
selected
address goes into the next available blank target field on the
target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
So it isn't a new record, but rather then next in a sequence? Not sure about
the acNext, try it - the SQL would be an UPDATE rather than an insert.

efandango said:
Susan,

It has just occured to me that you may have been slightly misled, i'm
referring to acNewRecord, the thing is, the target subform already has the
records, except the missing address field data that i want to get from the
Selector Form. Therefore I'm assuming that your original code should have
read:

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNext

Will your SQL suggestion still work, or can we still utilise the
'DoCmd...'
albeit with the significant acNext code?.


MY Main Form is called: Runs
My Target Subform is Called: frm_Run_Test
My Target Table is Called: tbl_Run_Waypoints_Test
My Target Control is Called: AnswerWaypoint

I feel like i am nearly there, and appreciate your help and patience on
this

regards

Eric
SusanV said:
I was afraid of that. You can use SQ pretty easily though. As I
understand
your code, the data you want to grab is in the control
Me.Waypoint_Selector.
I don't know the name of the table that the second form is based on, so
I'll
call it "tblTEMP", and the field "Waypoint." Give this a try (modifying
as
needed):

Private Sub Waypoint_Selector_Click()

Dim strWaypoint as string

strWaypoint = Me.Waypoint_Selector

If IsNull(Forms.[frm_Run_Test].[Waypoint_Combo]) Then

DoCmd.RunSQL "INSERT INTO tblTEMP (Waypoint) VALUES " & strWaypoint &
"'"
DoCmd.OpenForm "Your2ndForm"


Else
MsgBox "All fields are filled"
End If

End Sub

efandango said:
Hello Susan,

I tried this:

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNewRecord
Else
MsgBox "All fields are filled"
End If


End Sub


But it didn't work, it simple went to the:

MsgBox "All fields are filled"


:

You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though,
haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the
other
form
is based on, then requery or reopen the second form

--
hth,
SusanV

I figured out what the problem was, i didn't refer to the 'main'
form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target
form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next
available
blank field; Is it because the target is a continous form?, if so is
there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

:

Correct order? Does sorting not do this for you without the need
for
all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to
transfer
to
another form when the user clicks on an address. Eventually
creating
a
new
correct list.

The target address list must ultimately be in the correct order;
the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on
the
left
will be the Target Form, and the form on the right is the
Selector
Form.
Both
Forms are continuous forms because each list of addresses are
different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be
transferred
to
the
Target Form list. Eventually all available selections will be
chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the
selected
address goes into the next available blank target field on the
target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
Hi Susan,

Yes it is never a new record. Essentialy, I have two identical forms, one is
blank with the answers hidden (the target form); the other has the answers
(in random order), this is the Selector Form. The answer comes in a
combination of two fields; 'Direction' and 'Waypoint' laid out on a continous
form in this format

[Run_Direction] [Run_waypoint]
[Run_Direction] [Run_waypoint]
[Run_Direction] [Run_waypoint]

For example:
Turn Left Main Street
Turn Right South Street
Forward Spring Street
and so on...


Here is my Kludgy code cobbeld together with my limited coding knowledge,
and help from your good self and Marshall Barton on this forum. It is in two
parts, one for each control in order tha the user can fill the Target list
sequentially, moving left to right between each field has they work their way
through the list (I hope this makes sense...)

Here is my code so far for the 'Waypoint_Selector' Field on the Selector Form:

Private Sub Waypoint_Selector_Click()
If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector
'If the Target field is empty then place the Selection into the Target
Field

Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Waypoint_Combo.SetFocus
DoCmd.GoToControl "Waypoint_Combo"
'This gets the Focus on the Run Test Form/Waypoint Control

If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Me.Waypoint_Selector = Null
'This deletes the correct entry from the Waypoint Selections form

Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Direction_Combo.SetFocus
RunCommand acCmdRecordsGoToNext
'This goes to next Blank Direction Field record if correct

End If

If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Null
'This checks if the selection is wrong, if so, then reset (blank) the
waypoint target


End If

Else
Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Waypoint_Combo.SetFocus
RunCommand acCmdRecordsGoToNext

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector

If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Null

End If

Else
' MsgBox "All fields are filled"
End If
'End If
End If
End Sub

AND this is my code for the 'Run Direction' field on the same Selector Form:

Private Sub Direction_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Direction_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] = Me.Direction_Selector

Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Direction_Combo.SetFocus
DoCmd.GoToControl "Direction_Combo"
'This gets the Focus on the Run Test Form/Direction Control

If (Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] =
Forms.Runs.[frm_Run_Test].Form.[Run_Direction]) Then
Me.Direction_Selector = Null
'This deletes the correct entry from the Direction Selections form

Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Waypoint_Combo.SetFocus
'RunCommand acCmdRecordsGoToNext
'This goes to next Blank Waypoint Field record if correct

End If

If (Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_Direction]) Then
Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] = Null


End If

Else
Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Direction_Combo.SetFocus
RunCommand acCmdRecordsGoToNext
'This goes to next record if correct

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Direction_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] = Me.Direction_Selector

If (Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_Direction]) Then
Forms.Runs.[frm_Run_Test].Form.[Direction_Combo] = Null

End If

Else
MsgBox "All fields are filled"
End If
End If

End Sub









SusanV said:
So it isn't a new record, but rather then next in a sequence? Not sure about
the acNext, try it - the SQL would be an UPDATE rather than an insert.

efandango said:
Susan,

It has just occured to me that you may have been slightly misled, i'm
referring to acNewRecord, the thing is, the target subform already has the
records, except the missing address field data that i want to get from the
Selector Form. Therefore I'm assuming that your original code should have
read:

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNext

Will your SQL suggestion still work, or can we still utilise the
'DoCmd...'
albeit with the significant acNext code?.


MY Main Form is called: Runs
My Target Subform is Called: frm_Run_Test
My Target Table is Called: tbl_Run_Waypoints_Test
My Target Control is Called: AnswerWaypoint

I feel like i am nearly there, and appreciate your help and patience on
this

regards

Eric
SusanV said:
I was afraid of that. You can use SQ pretty easily though. As I
understand
your code, the data you want to grab is in the control
Me.Waypoint_Selector.
I don't know the name of the table that the second form is based on, so
I'll
call it "tblTEMP", and the field "Waypoint." Give this a try (modifying
as
needed):

Private Sub Waypoint_Selector_Click()

Dim strWaypoint as string

strWaypoint = Me.Waypoint_Selector

If IsNull(Forms.[frm_Run_Test].[Waypoint_Combo]) Then

DoCmd.RunSQL "INSERT INTO tblTEMP (Waypoint) VALUES " & strWaypoint &
"'"
DoCmd.OpenForm "Your2ndForm"


Else
MsgBox "All fields are filled"
End If

End Sub

Hello Susan,

I tried this:

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

DoCmd.GoToRecord acDataForm, Forms.Runs.[frm_Run_Test], acNewRecord
Else
MsgBox "All fields are filled"
End If


End Sub


But it didn't work, it simple went to the:

MsgBox "All fields are filled"


:

You can put the focus on a new record like so:

docmd.GoToRecord acDataForm,"YourOtherForm",acNewRecord

Not sure if you can use that with your current methodology though,
haven't
tested.

Better yet to use a SQL INSERT to add the record to the table the
other
form
is based on, then requery or reopen the second form

--
hth,
SusanV

I figured out what the problem was, i didn't refer to the 'main'
form.

But, I have an underlying problem that I can't get my head around:

This code will correctly place the selected address in the target
form

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

But this will not allow a second selction to go into the next
available
blank field; Is it because the target is a continous form?, if so is
there
a
way around this problem?

Private Sub Waypoint_Selector_Click()

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

ElseIf IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Me.Waypoint_Selector

Else
MsgBox "All fields are filled"
End If

End Sub

:

Correct order? Does sorting not do this for you without the need
for
all
this work for the user?

<confused>

I have a series of lists of addresses on a form that I want to
transfer
to
another form when the user clicks on an address. Eventually
creating
a
new
correct list.

The target address list must ultimately be in the correct order;
the
challenge is for the user to select the addresses in that order.

The way I want the form to work is to have two forms, the form on
the
left
will be the Target Form, and the form on the right is the
Selector
Form.
Both
Forms are continuous forms because each list of addresses are
different
lenghts.

The twist is that the Selector form addresses will be in a random
order,
and
by the user clicking on a selection, that address will be
transferred
to
the
Target Form list. Eventually all available selections will be
chosen,
making
a complete list.

The problem is that I can't figure out how to ensure that the
selected
address goes into the next available blank target field on the
target
form,
and doesn't fill in an already populated text box.

Does anyone know how I would do this with code?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top