form as input box

L

Loni - RWT

I have a combo with the limit to list set to true. I am trying to define an
On Not In List procedure to handle adding a new item when necesasary. I have
done this in other instances, the problem here is there are other fields
required in the underlying table. What I think I need to do is to open the
appropriate form, add the needed data and then come back to the current form.
This has probably been addressed somewhere but I haven't been able to find
anything that I think applies. If someone could point me in the right
direction I would greatly appreciate it.
Thanks so much!
 
K

Ken Sheridan

The key thing is to open the form in dialog mode as this pauses code
execution in the calling procedure until the form is closed (or hidden).
Here's an example for a combo box of cities whose NotInList event procedure
opens a form in which other fields in the Cities table can be added as well
as its name:

Private Sub cboCities_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

The new city name is passed to the frmCities form via the OpenArgs
mechanism. In the Open event procedure of the frmCities form the
DefaultValue property of the control bound to the City field is set to the
name of the new city with:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Note that the DefaultValue property of a control is always a string
expression regardless of the data type of the underlying field, which is why
the above code wraps the value in literal quotes. In this case its actually
not essential to do this, but in some cases its crucial (dates particularly)
so its prudent to always wrap the value in quotes. Setting the DefaultValue
property is better than setting the control's Value property as the former
does not initiate an edit, so the user can simply close the form to back out
if they decide not to add the new city record.

Ken Sheridan
Stafford, England
 
L

Loni - RWT

Thanks for your help. I copied your code and edited it to match my names but
the form is not opening and the data not added error comes up. I suppose I've
misunderstood something so here is my code:
Private Sub TruckID_NotInList(NewData As String, Response As Integer)
Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Is this a rental truck?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmRentalInfo", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure truck has been added
If Not IsNull(DLookup("TruckID", "tblTrucks", "TruckID = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Trucks table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If


End Sub
I set a breakpoint at the If statement to check the variables which all
seemed to be correct.

As to passing the new data to the control on the form in the open args, the
control's DefaultValue caused an error so I changed it to "Value" which was
the only apparent option. Here is that code:
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.TruckID.Value = """" & Me.OpenArgs & """"
End If

End Sub
I am using Access 2000 if that has any effect.
Thanks again for your help!
 
R

Ramona Padron

Loni - RWT said:
I have a combo with the limit to list set to true. I am trying to define
an
On Not In List procedure to handle adding a new item when necesasary. I
have
done this in other instances, the problem here is there are other fields
required in the underlying table. What I think I need to do is to open
the
appropriate form, add the needed data and then come back to the current
form.
This has probably been addressed somewhere but I haven't been able to find
anything that I think applies. If someone could point me in the right
direction I would greatly appreciate it.
Thanks so much!
Click to expand...
 
K

Ken Sheridan

The code looks OK at first sight. Its odd that the form is not opening at
all. Is there any code in its Open event procedure other than that which
sets the DefaultValue (or Value) property of the TruckID control? In
particular is there anything which is setting the return value of the Open
event's Cancel argument to True? You don't show any other code in the Open
event procedure in your last post, so I'm assuming the answer is no; in which
case I'm puzzled as to why the form does not open at all. At worst I'd have
expected a system generated error message if for some reason it can't open.

There are one or two assumptions implicit in your code, though I would not
expect anything arising from these to prevent the form from opening, or at
least not to do so without raising an error ( the 'not added to Trucks table'
message is not an error message as such, but a confirmation that the user
elected not to add the new record in the form).

1. The fact that you are passing the TruckID value to the form implies that
the combo box not only has that as its value but is also displaying it. This
differs from mine where the value of the combo box is the numeric CityID
foreign key field, but the value displayed in the control is the text City
value looked up from the Cities table, and it’s the text City value passed to
the form as its OpenArgs property. This is done by hiding the first column
of the control's RowSource by setting the first dimension of the control's
ColumnWidths property to zero.

2. You are enclosing the TruckID value in literal quotes when calling the
DLookup function. This suggests it’s a text data type rather than a number
data type, which doesn't need to be enclosed in quotes. If it’s a number
data type you just omit the literal quotes:

If Not IsNull(DLookup("TruckID", "tblTrucks", "TruckID = " & _
NewData)) Then

3. I'd recommend you stick with assigning the OpenArgs property's value as
the control's DefaultValue property (what error were you getting?), but if
you do assign it to the Value property then you don't need to enclose it in
quotes as that would make the quotes part of the value. Nor do you need to
specify the Value property as, this being a control's Default property, it
can be omitted:

If Not IsNull(Me.OpenArgs) Then
Me.TruckID = Me.OpenArgs
End If

Ken Sheridan
Stafford, England
 
L

Loni - RWT

Thanks again. The TruckID is a text field and when I tried without the
literal quotes I got a type mismatch error. I reset the Open Args to
TruckID.DefaultValue" and tried again. When I entered data not in the list,
I received a compile error:method or data member not found with the
".DefaultValue =" highlighted in blue. After changing this back, when
entering non-list data and selecting "Yes" it goes right to the "data not
added warning".
Thanks,
 
K

Ken Sheridan

The compile error suggests that TruckID, while a field in the form's
underlying recordset, is either not a control on the form, or the control
bound to the Truck ID field has a different name, e.g. txtTruckID, to its
field. The DefaultValue property in this context is a property of a control,
not a field. In the former case if you don't want to see the TruckID on the
form then add a TruckID control bound to it and set the control's Visible
property to False. In the latter case use the control's name when assigning
the value to its DefaultValue property. Either way you should then be able
to assign a value as its DefaultValue property.

This doesn't explain why the form isn't opening, though, which has me
puzzled. I don't think I can help further on this point remotely, but if you
want to mail me a file with the two forms and the relevant tables (they can
be empty; I wouldn't need the data itself), along with any queries used by
the forms, hopefully I'd be able to debug it. If you want to do this send it
to:

ken<dot>sheridan<at>dsl<dot>pipex<dot>com

Ken Sheridan
Stafford, England
 
L

Loni - RWT

You were right. I hadn't included the TruckID on the form. I then got a
"cannot assign a value" error and found the OpenForm code only needed 2 sets
of quotes. Everything is working great now.
If Not IsNull(Me.OpenArgs) Then
Me.TruckID.DefaultValue = "" & Me.OpenArgs & ""
End If
Thanks so much for all your help!
 
G

Gen

Hi there,

I found these posts very helpful as I am trying to do the same thing,
however its not working very smoothly yet. Here is my modified code:

Private Sub Antigen_NotInList(NewData As String, Response As Integer)

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl

strMessage = "'" & NewData & "' is not an available antigen name. Please
make sure this is a new antigen. " & vbCrLf & vbCrLf
strMessage = strMessage & "Do you want to add a new antigen?"
strMessage = strMessage & vbCrLf & vbCrLf & "Click Yes to link or No to
re-type it."


If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "Antigen Names", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure antigen has been added
If Not IsNull(DLookup("Antigen", "Antigen Names", "Antigen = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Antigen Names."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

So when I enter new data into Form 1, the message box pops up and if I click
Yes then Form 2 opens with the new entry where it should be etc. the problem
is that when I close Form 2, it doesn't save the new entry and the second
message box pops up saying "NewEntry was not added to antigen Names". What is
going on?!?!? I should also mention that I am a newbie, and don't know much
code at all.

Thanks in advance to anyone who can help!
 
U

UpRider

Gen, try
If Not IsNull(DLookup("Antigen", "Antigen Names", "Antigen = " &
chr$(39) _
& NewData & chr$(39))) Then

chr$(39) is a single quote and expressed this way does not need all the
confusing double double quote accompanyment.

HTH, UpRider
 
Top