Passing temporary data from textboxes to listbox

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

Guest

Help please!

I have 2 textboxes (txtAreaCode, txtPhoneNumber), and, a combobox
(cboPhoneDesc) on a form (frmEntry) that make up a phone number.

The user is to enter the data in the fields. But the user may have
multiple phone numbers to enter.

In this case I want the user to click a button (cmdAddNumber) that makes a
listbox pop-up that houses the data from txtAreaCode, txtPhoneNumber, and
cboPhoneDesc (as one row).

Then these fields on the form blank out so the user can enter new data.

When the user clicks cmdAddNumber this new data gets posted as another row
in the listbox and so on and so forth until the user has entered as many
numbers as required.

what code do i have to write to pass data from these fields on the form to
one single listbox (as a pop-up form) and be able to temporarily hold all
this data (so that later I can write it to a table)?

I'm useless at programming!

TIA
 
Not the way to do it. It will complicate your world. There are two
solutions. The first is the simplest, but not the most elegant, and has it's
limits. That would be to design your table and your form for multiple phone
numbers, say [HOME_PHONE], [CELL_PHONE], [WORK_PHONE], [FAX_PHONE]

The other is more in tune with proper database design, but takes a bit more
work. That is to create a child table for phone numbers. Then you would
need a subform to allow entry of as many or few phone numbers as you need for
an entry.
 
Thanks for the reply.

The description field (cboPhoneDesc) allows the user to select what type of
phone number it is.

The phone number table is already separate from the main table. I don't
want to make space on my form for those 4 different types of phone numbers
because often they're not all available, or someone may have multiple cell
numbers.

I was hoping for something that looks a little more elegant than a subform.

So it's not possible to pass temporary data from textboxes to a listbox?
Once the user has entered all the phone numbers and clicks an update button
all these numbers in the listbox will then be populated into the phone table
that's tied to the main table by a name ID.

TIA.
 
Okay, then yes, you could use a list box. What you would have to do is in
the after update events of the text box where you enter the phone number and
the the text box where you enter the description you would have to determine
if you have all the data necessary to create a record in the phone number
table. If you do, then append a new record to the phone number table and
requery your list box. This would assume the record source for the list box
is the phone number table.

b dubbin said:
Thanks for the reply.

The description field (cboPhoneDesc) allows the user to select what type of
phone number it is.

The phone number table is already separate from the main table. I don't
want to make space on my form for those 4 different types of phone numbers
because often they're not all available, or someone may have multiple cell
numbers.

I was hoping for something that looks a little more elegant than a subform.

So it's not possible to pass temporary data from textboxes to a listbox?
Once the user has entered all the phone numbers and clicks an update button
all these numbers in the listbox will then be populated into the phone table
that's tied to the main table by a name ID.

TIA.

Klatuu said:
Not the way to do it. It will complicate your world. There are two
solutions. The first is the simplest, but not the most elegant, and has it's
limits. That would be to design your table and your form for multiple phone
numbers, say [HOME_PHONE], [CELL_PHONE], [WORK_PHONE], [FAX_PHONE]

The other is more in tune with proper database design, but takes a bit more
work. That is to create a child table for phone numbers. Then you would
need a subform to allow entry of as many or few phone numbers as you need for
an entry.
Click to expand...
Click to expand...
 
Thanks for the replies.

I've got the theory behind it - I'm looking for some help with the code.

What's the code i need to pass data from text boxes to some temporary
variable (I don't really want to make a new temp table) and then into the
list box. Keeping in mind that this has to be able to happen numerous times
(ie it's adding to the items already in the list box).

Then whenever the user is ready he/she hits a button and the list box gets
written to a table.
 
I'm actually doing something similar in my current project. My users may need
to enter several children for a single assessment. I give them three text boxes
for Last Name, First Name and DOB. On clicking Add Child I move that data to a
list box and clear the text boxes for the next entry. Then I parse out the
contents of the list box to write records to the appropriate table(s). Below is
the code I use to move the data to the list box. Note that I concatenate the
children's names before putting them into the list box. I didn't need to do
that but it just looked better and I have a nice parsing routine to break them
out again for the fields in the table. If you need help pulling the data out to
write to your table let me know and I'll post what I'm doing for that, as well.

Watch out for line wrap. Hope it helps.

<code>
Private Sub cmdAddChild_Click()
On Error GoTo ERH
Dim sChildName As String
Dim dtDOB As Date

' Concatenate children's name and assign to variable
sChildName = Chr(34) & Me.txtChildLName & ", " & Me.txtChildFName & Chr(34)
' Assign DOB to variable
dtDOB = Me.txtDOB

' Check for Null or empty
If Not IsNull(sChildName) Or sChildName <> "" Then
Me.lstChild.SetFocus
If Me.lstChild.RowSource = "" Then
Me.lstChild.RowSource = sChildName & "; " & dtDOB
Else
Me.lstChild.RowSource = Me.lstChild.RowSource & "; " & sChildName & "; "
& dtDOB
End If
' Clear contents of text boxes for next entry
Me.lstChild.Requery
Me.txtDOB.SetFocus
Me.txtDOB = ""
Me.txtChildFName.SetFocus
Me.txtChildFName = ""
Me.txtChildLName.SetFocus
Me.txtChildLName = ""
End If

ExitPoint:
Exit Sub

ERH:
Debug.Print Err.Number & ": " & Err.Description
'Stop ' Remove comment for debugging
Resume ExitPoint
End Sub
</code>
 
That's excellent! Thanks a lot.

I threw your code into a new database and built the basic form (3 txt boxes
and a list box - named like you have in the code). The code compiles and
executes without error - but nothing shows up in the list box.

I assumed:
Me.lstChild.RowSource = sChildName & "; " & dtDOB
is the code that once I step through it should populate lstChild but nothing
happens?

Any ideas?

I appreciate your help!
 
Ok - that was dumb.

I just needed to change my Row Source Type to 'Value List.'

I'd love to see what you've done for pulling the data out and writing it to
the table, RD.

Thanks for your help so far. That's exactly what I was looking for.
 
Ok - that was dumb.

I just needed to change my Row Source Type to 'Value List.'
Click to expand...

Mea culpa ... I should have indicated that
I'd love to see what you've done for pulling the data out and writing it to
the table, RD.
Click to expand...

Awww ... you can call me R.

So, ok, here are some snippets. The actual procedure is rather large and
contains stuff not pertinent to what you're trying to do. And, I've been
building this app on the run, under pressure (I brought it home to work on it
over the weekend) and with constant interruption so, there is a bunch of really
ugly code in there.

Again, watch for line wrap.

First, I step through the list box and SELECT everything (did I mention that I
have the Multiselect property set to Simple? No? Ooops.)

' We'll declare a few variables
Dim ctl As Control
Dim vChild As Variant, n As Variant
Dim sChildLName as String, sChildFName As String
Dim dtDOB As Date
Dim sSql As String
Dim sRetVal As String

' Loop through list box items and set all to Selected.
For n = 0 To Me.lstChild.ListCount - 1
Me!lstChild.Selected(n) = True
Next n

' Loop through again to grab selected items
Set ctl = Me!lstChild
For Each n In ctl.ItemsSelected
vChild = ctl.ItemData(n) ' Get child's name from listbox
sChildLName = fParseLastName(CStr(vChild)) ' Parse last name
sChildFName = fParseFirstName(CStr(vChild)) ' Parse first name
dtDOB = Me.lstChild.Column(1, n)
' Build SQL statement
sSql = "INSERT INTO tChild ("
sSql = sSql & "ChildLName, ChildFName, DOB) "
sSql = sSql & "Values ('"
sSql = sSql & sChildLName & "', '" & sChildFName & "', #" & dtDOB & "#)"
' Write the record
sRetVal = fSaveRecord(sSql)
Next n

Below are the functions referenced in the code above. The name parsing
functions are bare bones and could probably use some tweaking.
The save record function's only job is to open the database and execute a SQL
statement.

Function fParseFirstName(sName As String) As String
' Extract first name from full name
If InStr(1, sName, ",") > 0 Then
fParseFirstName = Right$(sName, Len(sName) - InStr(1, sName, ",") - 1)
Else
fParseFirstName = Left(sName, InStr(1, sName, " ") - 1)
End If
End Function

Function fParseLastName(sName As String) As String
' Extract last name from full name
If InStr(1, sName, ",") > 0 Then
fParseLastName = Left$(sName, InStr(1, sName, ",") - 1)
Else
fParseLastName = Right(sName, Len(sName) - InStr(1, sName, " "))
End If
End Function

Function fSaveRecord(sSql As String) As String
On Error GoTo ERH
Dim db As DAO.Database

Set db = CurrentDb()
db.Execute sSql
fSaveRecord = "True"

ExitPoint:
Set db = Nothing
Exit Function

ERH:
Debug.Print Err.Number & ": " & Err.Description
fSaveRecord = Err.Number
Resume ExitPoint
End Function
 
Hi R - thanks for all the help so far.

I've added the code to a save button on my form but now it errors out when I
hit:

dtDOB = Me.lstChild.Column(1, n)

The message is: "Invalid use of Null"

Me.lstChild is null.

Not sure what's going wrong or what value I should be expecting.

In my list box I get the Childs name on one line and his/her date of birth
on the next line. The parsing variables seem to be working.

Oh - and thanks for the tip on the multiselect property - I'm sure that
would have stumped me!
 
I think I left out some more info :-( I ended up working myself so hard over
the weekend I made myself sick and missed work, yesterday.

Anyway, the child's name and DOB should be on the same line. Go to the
properties box and change the Colomn Count property to 2. That should get
things going.
 
Excellent - it works like a charm! I definitely have a couple days work
ahead of me now making my db work like yours! Thanks for all your help -
it's really appreciated!
 
Glad I could help.

Regards,
RD

Excellent - it works like a charm! I definitely have a couple days work
ahead of me now making my db work like yours! Thanks for all your help -
it's really appreciated!
Click to expand...
 
Back
Top