Form Code to update the table

[kay]

I am beginner to ms access.
I have created form and then insert a tab control (3 tabs) in a form.
I am trying to write a vba code to add/update data into the table.
I have created a table – ‘ Resources†which is currently blank.
My fields are Name, EmpID, OriginalDt(Date when the first time item is
entered), UpdateDt (Date of last update), UpdatedBy(Name of Emp who last
update the item)
The data entry into this table comes from Form (user will access this form.
They don’t have access to the table)
So I have created above fields in a form. Now when user enters all above
entries in a form and when they click on ‘Update’ button, these entries
should go in Resources table.

I tried using sample codes but not giving me appropriate result.

Thanks

 

[Jeff Boyce]

If you "painted" controls on your form without "wiring" them to the
underlying table (or better yet, to a query against that table), then Access
isn't smart enough to know that you want what goes into those controls to
"belong" to the table.

Are your controls "bound"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[kismert]

You are working too hard. You want a "bound form" to do the work for you.

You do this by binding the form to the table via a query:
1. Make a query, qryResources, which returns all the fields in table
Resources, (Why? call it good practice)
2. In your form, set the Recordsource property to qryResources.
3. For each control in the form, set the Control Source to the appropriate
field in the query.

There! You now have a basic form that will read and write data to a table.

Of course, there is a lot more twiddling you will want to do with the form
properties to get something close to what you want, but I leave you with your
perseverance and the trusty F1 key.

-Ken

 

[kay]

Thanks,
I tried this way but what happens that first time when the table was blank
it worked fine. when you enter data in forms it goes to table but next time
when you open the form it shows previous entries and that i do not want to
show to user.
they should not know what is there in the table.

hope i am able to explain my point.

 

[Jeff Boyce]

If the form is bound to the table (or, as I suggested previously, to a query
against that table), then opening the form opens a window to the records in
that table.

If you want the form to ONLY all data entry (and not display existing
records), you need to change the Data Entry property of the form.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jack Leach]

set the DataEntry property of the form to True... this lets them add new
records only and not view any existing ones.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[kay]

Thanks for the quick reply.

All these suggestions are helpful. But still that is not the solution I am
looking for.
As I do not want user to see how many and what records are there in the
table, but at the same time if they enter ResourceID (unique –PK) they should
be able to view the details for that ID. So its not about adding new data,
its about updating existing data and keeping track of the updates.
Data is already there in the table, user will be updating data and adding
comments to the table.
So here the picture of the form I am trying to design. (this is the simple
way I have not made fancy yet, once code and everything is set up, I will try
to look it fancy)
First – I have created the blank table ‘tblResources’ – data entry coming
from ‘frmResources’ will be stored in this table.
User will have access to ‘frmResources’. So when they enter ID the related
fields details will pop up – comments, date, ..etc.
At the bottom of the form I have created two buttons – update and Exit.
So if user needs to update the information ( for eg. Update the comments)
they will change in the form and then when they click on update – it should
save the update into the table and whey they click on Exit – the
form(application) will just quit without saving the change.

Sorry for making it complicated but this is what I am trying to design it.
appreciate your help.
Thanks

 

[kay]

Sorry forgot to explain this point that
frmResources - form will use by two group of people.
1. who will use this form to enter the data
2. who will uer this form to update the data.

Thanks,
-------------------------

 

[Jack Leach]

1) disable all of the navigation buttons from the Properties of the form...
2) provide an unbound textbox to use as a search box for an ID to jump to...
3) provide a "New Record" button for them...

One should be self explanitory... there is a property called "Navigation
Buttons" (or the like) which you will set to No. This removes the standard
nav buttons (and record counter that comes with it).

Two is easy enough... but a button on the form, and in the Click event of
the button put this line of code:

DoCmd.GotoRecord, , acNewRec


Three is a pretty standard operation as well. Use the Bookmark property of
a recordsetclone of the form to jump to the record, if found...

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Me.ctlGoToTextbox.SetFocus
With rs
.FindFirst "[yourIDfield] = " & Nz(Me.ctlGotoCheckbox.Text, "")
If Not .NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record Not Found!"
End If
End With
Set rs = Nothing


Personally I use a textbox with a "Go" button next to it and set this code
to run from the Go button click or from the KeyDown event of the text box
(check for vbEnter).

I believe this should accomplish what you seek... the users will be able to
go to add a record, or edit any record that is part of the recordset, but not
have the option to navigate the records (aside from entering every ID in the
search box), nor will they see how many records are in the set.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[kay]

Thanks for helping me out..
I was trying these codes on the form,
But when I enter the resourceID it does not show other information of ID in
other txboxes and combo box fields.
I created the button and add this code under event proc - DoCmd.GotoRecord,
, acNewRec
But this does not help to pop up other information of that ID – dateof
followup, Comments,..etc

1) disable all of the navigation buttons from the Properties of the form...
2) provide an unbound textbox to use as a search box for an ID to jump to...
3) provide a "New Record" button for them...

One should be self explanitory... there is a property called "Navigation
Buttons" (or the like) which you will set to No. This removes the standard
nav buttons (and record counter that comes with it).

Two is easy enough... but a button on the form, and in the Click event of
the button put this line of code:

DoCmd.GotoRecord, , acNewRec


Three is a pretty standard operation as well. Use the Bookmark property of
a recordsetclone of the form to jump to the record, if found...

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Me.ctlGoToTextbox.SetFocus
With rs
.FindFirst "[yourIDfield] = " & Nz(Me.ctlGotoCheckbox.Text, "")
If Not .NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record Not Found!"
End If
End With
Set rs = Nothing


Personally I use a textbox with a "Go" button next to it and set this code
to run from the Go button click or from the KeyDown event of the text box
(check for vbEnter).

I believe this should accomplish what you seek... the users will be able to
go to add a record, or edit any record that is part of the recordset, but not
have the option to navigate the records (aside from entering every ID in the
search box), nor will they see how many records are in the set.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Sorry forgot to explain this point that
frmResources - form will use by two group of people.
1. who will use this form to enter the data
2. who will uer this form to update the data.

Thanks,

 

[kay]

mean while I solve previous problem, I am trying to create login form.
and here is the code I am using

on Login form I have two text box and 1 login button
UserName: txtUserNm
Password: txtPassword
-------------------------------------------------------------
Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click

Dim rs As DAO.Recordset
Dim strUser As String
Dim strPassword As String

txtUserNm.SetFocus
strUser = txtUserNm
txtPassword.SetFocus
strPassword = txtPassword

If Not IsNull(Me.txtUserNm) And Not IsNull(Me.txtPassword) Then
Set rs = CurrentDb.OpenRecordset("Select
tblEmpID.EmpID,tblEmpID.EmpPassword from tblEmpID where EmpID = '" & strUser
& "' And EmpPassword = '" & strPassword & "'", dbOpenSnapshot)

If rs.EOF Then
DoCmd.OpenForm "frmResourceV2"
Else
MsgBox "In Correct"
End If
End If
Exit_cmdLogin_Click:
Exit Sub

Err_cmdLogin_Click:
MsgBox Err.Description
Resume Exit_cmdLogin_Click

End Su
----------------------------------------------------------------------------------
I was just checking that the code is working properly or not.
When I dont enter anything- it gives message " Invalid Use Of Null"
When I enter anything it opens the form without checking the values in the
table.

please help..
thanks.

Thanks for helping me out..
I was trying these codes on the form,
But when I enter the resourceID it does not show other information of ID in
other txboxes and combo box fields.
I created the button and add this code under event proc - DoCmd.GotoRecord,
, acNewRec
But this does not help to pop up other information of that ID – dateof
followup, Comments,..etc

1) disable all of the navigation buttons from the Properties of the form...
2) provide an unbound textbox to use as a search box for an ID to jump to...
3) provide a "New Record" button for them...

One should be self explanitory... there is a property called "Navigation
Buttons" (or the like) which you will set to No. This removes the standard
nav buttons (and record counter that comes with it).

Two is easy enough... but a button on the form, and in the Click event of
the button put this line of code:

DoCmd.GotoRecord, , acNewRec


Three is a pretty standard operation as well. Use the Bookmark property of
a recordsetclone of the form to jump to the record, if found...

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Me.ctlGoToTextbox.SetFocus
With rs
.FindFirst "[yourIDfield] = " & Nz(Me.ctlGotoCheckbox.Text, "")
If Not .NoMatch Then
Me.Bookmark = rs.Bookmark
Else
MsgBox "Record Not Found!"
End If
End With
Set rs = Nothing


Personally I use a textbox with a "Go" button next to it and set this code
to run from the Go button click or from the KeyDown event of the text box
(check for vbEnter).

I believe this should accomplish what you seek... the users will be able to
go to add a record, or edit any record that is part of the recordset, but not
have the option to navigate the records (aside from entering every ID in the
search box), nor will they see how many records are in the set.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Sorry forgot to explain this point that
frmResources - form will use by two group of people.
1. who will use this form to enter the data
2. who will uer this form to update the data.

Thanks,
-------------------------

:

Thanks for the quick reply.

All these suggestions are helpful. But still that is not the solution I am
looking for.
As I do not want user to see how many and what records are there in the
table, but at the same time if they enter ResourceID (unique –PK) they should
be able to view the details for that ID. So its not about adding new data,
its about updating existing data and keeping track of the updates.
Data is already there in the table, user will be updating data and adding
comments to the table.
So here the picture of the form I am trying to design. (this is the simple
way I have not made fancy yet, once code and everything is set up, I will try
to look it fancy)
First – I have created the blank table ‘tblResources’ – data entry coming
from ‘frmResources’ will be stored in this table.
User will have access to ‘frmResources’. So when they enter ID the related
fields details will pop up – comments, date, ..etc.
At the bottom of the form I have created two buttons – update and Exit.
So if user needs to update the information ( for eg. Update the comments)
they will change in the form and then when they click on update – it should
save the update into the table and whey they click on Exit – the
form(application) will just quit without saving the change.

Sorry for making it complicated but this is what I am trying to design it.
appreciate your help.
Thanks


:

set the DataEntry property of the form to True... this lets them add new
records only and not view any existing ones.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



:

Thanks,
I tried this way but what happens that first time when the table was blank
it worked fine. when you enter data in forms it goes to table but next time
when you open the form it shows previous entries and that i do not want to
show to user.
they should not know what is there in the table.

hope i am able to explain my point.

:

You are working too hard. You want a "bound form" to do the work for you.

You do this by binding the form to the table via a query:
1. Make a query, qryResources, which returns all the fields in table
Resources, (Why? call it good practice)
2. In your form, set the Recordsource property to qryResources.
3. For each control in the form, set the Control Source to the appropriate
field in the query.

There! You now have a basic form that will read and write data to a table.

Of course, there is a lot more twiddling you will want to do with the form
properties to get something close to what you want, but I leave you with your
perseverance and the trusty F1 key.

-Ken

 

[kay]

Thanks jeff for your suggestions.

currently I am working on Login form where I want user to enter username &
Password.
I am trying so many codes but not working.
I have tblEmpID - fields are : EmpName,EmpID,EmpPassword
I have tried two ways - 1. created a query to get EmpID and EmpPassword from
EmpID table whrer EmpID - criteria is forms!frmLogin!txtEmpID
2. ubbound taxboxes

but both ways are not working - I think i am doing some silly mistake in
writing code.

in my previous post i post the code which I tried earlier.
Please help to write the login code so when user click on the button, it
should valideate the valuse with the table and then open the main form.

Thanks

 

[Jeff Boyce]

Please post what you are trying so we can offer our ideas on why it may/may
not work.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[kay]

I am creating a application to keep a track of resources
As I have explained in my previous post,
My main form – frmResources – which I will put as default form when user
opens the application.
This form will be used by to group of people – One group will use this form
to enter the data and other group will use same form to edit the data.

I am working on this form. In this form I have not make – DataEntry set to
“Yes†as this form will use to update the data as well.
Second form I am working on to create Login for users.

I have created table call tblEmpID which has fields such as
EmpID,EmpName,EmpPassword,EmpEmail,EmpDept.

In the Login form I have created two textboxes and 1 Login Button.
I have put code on click for login button . but its not working properly.
This code is not validating user info. What ever I enter it opens the main
form.
I have posted the code in my previous post. So I don’t know what’s wrong
with that code.

I am working on to create other forms as well, but first I want to get done
these two forms.

So please guide me. I don’t mind starting from scratch.
Thanks!

 

[Jeff Boyce]

Let me re-phrase ...

Please post the code you are trying.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[kay]

ok thanks, I have access2003
Lets first work on Login form:
here is the code i am trying.
When you leave blank in username and password it pops up message ( I have
put that message as I know that cursor goes to On Error GoTo
Err_cmdLogin_Click)

with this code problem is that when you enter any value which in not in the
EmpID table, it opens the form. it means it is not validating user input.
txtUserNm - is unbound text box
txtPassword - is unbound text box
cmdLogin - is command button
----------------
Private Sub cmdLogin_Click()
On Error GoTo Err_cmdLogin_Click

Dim rs As DAO.Recordset
Dim strUser As String
Dim strPassword As String

txtUserNm.SetFocus
strUser = txtUserNm
txtPassword.SetFocus
strPassword = txtPassword



If Not IsNull(Me.txtUserNm) And Not IsNull(Me.txtPassword) Then
Set rs = CurrentDb.OpenRecordset("Select
tblEmpID.EmpID,tblEmpID.EmpPassword from tblEmpID where EmpID = '" & strUser
& "' And EmpPassword = '" & strPassword & "'", dbOpenSnapshot)

If rs.EOF Then
DoCmd.OpenForm "frmResourceV2"
Else
MsgBox "In Correct"
End If
End If
Exit_cmdLogin_Click:
Exit Sub

Err_cmdLogin_Click:
MsgBox "Enter the UserName & Password"
'MsgBox Err.Description
Resume Exit_cmdLogin_Click


End Sub