Save unbound and bound control value in a form into table

[Guest]

I have a table "Student". The primary key is combination of telephone and
sequence number.

I have a form to entry all students infomation. It has about 30 fields to
capture information. Some of bound are bound and some are unbound field. As
soon as people enter the save button, I want to save all text box values to
table and then show blank on the form. I would like to use best way to do
this.

I tried to use me.dirty=false, it doesn't work. I also tried to use the
insert in sql. But it seems like 30 values needed to write and possible has
problem in null values problem.

Do you have any better solution and codes? Thank you so much.

Jen

 

[Steve]

Two points about your post ---
1. The fact that your table has 30 fields raises a bright red flag that
your table(s) are not designed correctly.
2. A form directly or indirectly has a table as its recordsource. Any data
entered in a form gets saved to that table. Trying to save data in unbound
fields to a table does not make sense.

You need to examine your database in light of the above. If you need help in
this regard, post back.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

I have a table "Student". The primary key is combination of telephone and
sequence number.

I have a form to entry all students infomation. It has about 30 fields to
capture information. Some of bound are bound and some are unbound field. As
soon as people enter the save button, I want to save all text box values to
table and then show blank on the form. I would like to use best way to do
this.

I tried to use me.dirty=false, it doesn't work. I also tried to use the
insert in sql. But it seems like 30 values needed to write and possible has
problem in null values problem.

Do you have any better solution and codes? Thank you so much.

Jen

Why are some fields unbound and some bound? You're making your job more
difficult - a simple bound form will a) store the data in the table with no
code at all and b) blank all the fields (or set them to their default value)
when you move to the new record. Obviously setting me.dirty = false won't (by
itself) do anything with the unbound fields - since you're using unbound
fields, it's your responsibility to do something with them, since Access has
no way to know where the data should be put!


John W. Vinson [MVP]

 

[Guest]

Thank you for the suggestions.

I have a student table. The data field including:
(StudentID,name,student_tel, address, email, DOB, gender, grade,
father_info...., mother_info..., donotcall, donotemail, ...........).
StudentID is primary key (phone-squence number, eg: 2011234567-001,
2011234567-002) since one student only assciates one phone number and school
might have different student under same phone number. This is why I added on
sequence number. It is calculated automatically when phone is entered and
save in module level variable. The code is here:

Private Sub Student_Tel_BeforeUpdate(Cancel As Integer)
Dim varID As Variant
Dim seq As String
If Len(Me.Student_Tel) <> 10 Then
MsgBox "Please enter 10 digits numbers"
Cancel = True
End If
varID = DLookup("StudentID", "Student", " Student_Tel = " & Me.Student_Tel)
If IsNull(varID) Then
id = Str(Me.Student_Tel) & "-" & "001"
Else
seq = Str(Val(Right$(varID, 3)) + 1)
id = Str(Val(Left$(varID, 10))) & "-" & seq
End If
Me.StudentID = id
End Sub

I don't know how to correctly save this information. Here is my save code.
But i don't think it is good way to capture all form values in current object
"me.".

Private Sub Save_Click()
Dim mydb As Database
Dim myset As Recordset
Dim mysql As String
Set mydb = DBEngine.Workspaces(0).Databases(0)

mysql = "insert into student (StudentID, Student_Name_Ch, Student_Name_En,
Student_Address, " & _
"Student_Tel, Student_Email, DOB, Gender ) values(" _
& "'" & Trim(Me.StudentID) & "'," & "'" & Trim(Me.Student_Name_Ch) & "'," _
& "'" & Trim(Me.Student_Name_En) & "'," & "'" & Trim(Me.Student_Address) &
"'," _
& "'" & Me.Student_Tel & "'," & "'" & Trim(Me.Student_Email) & "'," _
& "'" & Trim(Me.DOB) & "'," & "'" & Trim(Me.Gender) & "'" &
")"......................
...........................................

DoCmd.RunSQL mysql
End

Thanks.

Jen

 

[Guest]

Thank you so much for suggestion.

I will redesign my database to cut the data fields into two tables. But I
still concern one thing. If I have a control value coming from another two
control combinations, (for example: name1||name2 as value of name3 in a
table), how this data field be saved? Thanks.

Jen

 

[John W. Vinson]

I will redesign my database to cut the data fields into two tables. But I
still concern one thing. If I have a control value coming from another two
control combinations, (for example: name1||name2 as value of name3 in a
table), how this data field be saved? Thanks.

You can save it with code - BUT if the field logically and legitimately has
two independent "chunks" then it should not be stored in one field in *any*
table. It's much, much easier to concatenate two fields for display than to
split an inappropriately combined field. One basic principle is that fields
should be "atomic" - containing only one piece of information. For example,
people's names are almost always best stored in multiple fields - Title,
Firstname, MiddleName, LastName, Suffix.

Perhaps you have an exception; if so, please indicate what the name1, name2
and name3 controls actually represent.

John W. Vinson [MVP]

 

[Guest]

Thank you for reply. John.

I have this example. I have a data field student_phone in student table. I
want to create another field by using student_phone and add on sequence
number (like 2011234567-001) as another data field value. This data field
make the record to be unique for search. Maybe it is not good example for
creating meaning data field. But sometime, people create dummy variables to
capture information. How to same calculated filed? Thanks.

 

[John W. Vinson]

Thank you for reply. John.

I have this example. I have a data field student_phone in student table. I
want to create another field by using student_phone and add on sequence
number (like 2011234567-001) as another data field value. This data field
make the record to be unique for search. Maybe it is not good example for
creating meaning data field. But sometime, people create dummy variables to
capture information. How to same calculated filed? Thanks.

There is no need, and no good reason, to store this redundant calculated field
in ANY table.

You can create a unique index on one field - or two fields - or on *ten*
fields.

If you have a field StudentPhone and a (separate) field StudentPhoneSeq,
Integer type, you can put a calculated field in a Query or in a control source
of a textbox:

[StudentPhone] & "-" & Format([StudentPhoneSeq], "000")

to *display* the composite; it is neither necessary nor beneficial to store
the composite.

John W. Vinson [MVP]

 

[Guest]

Thanks. John

Thank you for reply. John.

I have this example. I have a data field student_phone in student table. I
want to create another field by using student_phone and add on sequence
number (like 2011234567-001) as another data field value. This data field
make the record to be unique for search. Maybe it is not good example for
creating meaning data field. But sometime, people create dummy variables to
capture information. How to same calculated filed? Thanks.

There is no need, and no good reason, to store this redundant calculated field
in ANY table.

You can create a unique index on one field - or two fields - or on *ten*
fields.

If you have a field StudentPhone and a (separate) field StudentPhoneSeq,
Integer type, you can put a calculated field in a Query or in a control source
of a textbox:

[StudentPhone] & "-" & Format([StudentPhoneSeq], "000")

to *display* the composite; it is neither necessary nor beneficial to store
the composite.

John W. Vinson [MVP]