still confused on cmd button

  • Thread starter Thread starter NYDIA
  • Start date Start date
N

NYDIA

I posted a question before and got a response, but am
still confused. Here's my problem. I'm creating a dba to
keep track of clients in a program. There is a main form
which has name, address, etc. I have a questionaire form
that has about 20-25 questions and will be filled out
atleast 4 x's (if not more for each client). The 2 forms
are linked by clientID. I used the wizard to create a
command button to open the questionaire form and that
works, but what I need is when the questionaire form is
open, that is opens for the current record. what is
happening is if i click on the command button it opens the
form and you can't see who the questionaire is for, so you
don't know what clientid to put. I tried using a tab
control, but i would like to use cmd button instead (cmd
button seems more user friendly). Can someone please walk
me through the process to make this work.

is there a way that when the cmd button is pressed that it
automatically puts the current clientID in the form? any
help is greatly appreciated
 
If the form that your command button is opening is a blank one, then you can
just add a line of code after the OpenForm line that says:
[Forms]![QuestionairreForm]![ClientID] = [Forms]![MainForm]![ClientID]
 
Hi,
Some things are not clear:
"will be filled out
at least 4 x's (if not more for each client). "

What does that mean? They will not answer all the questions in one go, but
will
answer some, close the form and come back to it later?

"The 2 forms
are linked by clientID."

Does this mean that somewhere, you have two tables, one with ClientId as a
primary key
and one with ClientId as a foreign key? This is important, your data lives
in tables, not in forms.
Forms just display the data. If you want to 'link' two forms, what are the
Record Sources for the
two forms?

Finally, you can use the OpenArgs parameter of the OpenForm method to pass
the clientid.

Example, let's say the control that displays the clientid on both forms in
called txtClientId.

So in your OpenForm statement:

DoCmd.OpenForm "form2",,,,,,Me.txtClientId

Now, there are few things to consider on the open event of form2.
This could be a client who is answering for the first time, so we need to go
to
a new record and insert the clientid, or this could be a returning client,
in which case
we have to open the form to the existing record.

So.. in the Open event of form2:

If Me.OpenArgs <> "" Then
Me.RecordsetClone.FindFirst "clientid = " & Lng(Me.OpenArgs)
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtClientId = Me.OpenArgs
End If
End If

It's all air code at this point (not tested). I've assumed that your
clientid is a number of some sort (integer or a long).
You'd have to substitute the correct control and form names.

Hopefully this will get you started.

HTH
Dan Artuso, MVP
 
this seems to work, but here is my new problem. When i
click on the command button, if a questionaire is already
filled out, it brings that form up, but if you try to fill
out a new questionaire, you have to put in the clientID
(agian, that could lead to error when entering, if
clientID is 1 and someone types 2), is there a way to fix
this. Your instructions were clear thanks a alot.
-----Original Message-----
If the form that your command button is opening is a blank one, then you can
just add a line of code after the OpenForm line that says:
[Forms]![QuestionairreForm]![ClientID] = [Forms]! [MainForm]![ClientID]

I posted a question before and got a response, but am
still confused. Here's my problem. I'm creating a dba to
keep track of clients in a program. There is a main form
which has name, address, etc. I have a questionaire form
that has about 20-25 questions and will be filled out
atleast 4 x's (if not more for each client). The 2 forms
are linked by clientID. I used the wizard to create a
command button to open the questionaire form and that
works, but what I need is when the questionaire form is
open, that is opens for the current record. what is
happening is if i click on the command button it opens the
form and you can't see who the questionaire is for, so you
don't know what clientid to put. I tried using a tab
control, but i would like to use cmd button instead (cmd
button seems more user friendly). Can someone please walk
me through the process to make this work.

is there a way that when the cmd button is pressed that it
automatically puts the current clientID in the form? any
help is greatly appreciated
Click to expand...


.
Click to expand...
 
I have a few tables and each table has a clientID field,
the main table's clientID is an autonumber(primarykey) and
the other tables clientID are number (long inte).The
questionaire should be filled out about every 3 months, so
if a client stays in the program for 1 year it's filled
out 4 times. They should answer all the question at one
time, but if the employee gets busy, they might close the
form and come back to it later. Most of the questions are
yes/no checkboxs and then a comment field (so that
employee than enter notes about the client). Hope I'm
explaining this ok, i'm new to codes and am pretty much
learning by this newsgroup and trial and error. your help
is greatly appreciated
 
Whew. You've an interesting challenge. I'd suggest
giving the user 2 ways to approach this:
1. Continue with existing questionnaire
2. Begin new questionnaire
Each option has its own process, and should be handled
separately.

I picture myself looking at the client main form. What is
it I see that prompts me to continue with an EXISTING
questionnaire? Perhaps a listbox containing that client's
questionnaires, but you'll need a way to identify one from
the next (date it was started perhaps).

What is it I see that prompts me to create a new
questionnaire? Most likely a command button that opens
the questionnaire form to a new record and inserts the
clientid for you. Remember, you don't need to include the
clientid field from your client table, but rather the
clientid (foreign key) from your questionnaire table. You
don't want a primary key field on a "sub" form, it can
cause all kinds of problems.

Look further into understanding data storage, tables and
relationships. Understand that the tables store the data,
and the forms just show you the data the way you want to
see it, and allow you to insert new, edit existing or
delete it.
 
that makes sense on having a list box for all
questionaires on the specific client and a cmd button for
a new questionaire, NOW, the question is, what are the
codes to do that, is there a book or website that i can
learn more about codes, learn what openargs and all that
means, cuz i really dont thanks for all your help
 
Hi,
Okay, the big question is then, how are you identifying the questions filled
out by the clients?
I mean, clientid isn't enough if you have the same client responding to the
questions more than once.
You would need something like a DateFilledOut field.

The code I supplied will not work properly because it relied only on
clientid.
In fact, it has to be more complex. How will you know whether a client has
completed
all the questions and is now filling it out a second time, or is simply
returning to complete all the
questions for the first time?

I can't answer that because I don't know what your table structure is.
I'm of the feeling that what you're asking doesn't have a simple step by
step answer,
and this is why you're finding it difficult.

HTH
Dan Artuso, MVP
 
My tables are all linked by clientID, the main table's
client id is the primary key. On the questionaire form,
it does have a field for date completed. Each time that
the employee fills out the questionaire, they will put in
a date. I guess what i am trying to achieve is when the
command button is pressed, if the client has serveral
questionaires filled out the employee can view them, but
if they want to fill out a new questionaire and click on a
new questionaire command button, that a blank questionaire
will pop up with the current clientID already filled in.
 
I would recommend a book on VBA. Remember, you're dealing
with Access, which uses Visual Basic for Applications (or
VBA), not Visual Basic. While they are basically the same
language, VBA is actually a subset of VB, and a book on
Access and VBA would be helpful.

Here's a highlight.

Private Sub lstMyListBox_Click()

Dim lngQuestionnaire as Long
Dim strSQL as String

lngQuestionnaire = lstMyListBox.Column(0)
strSQL = "[QuestionnaireID] = " & lngClientQ
DoCmd.OpenForm "frmClientQuestionnaires", acNormal,,
strSQL

End Sub

That code passes the value from the questionnaire you
clicked on (the column(0) would be the first column of the
listbox's RowSource property, the questionnaire's ID) to
the variable. Then it passes a SQL string to a string
variable that incorporates that questionnaire's ID
number. Then it calls the OpenForm method, instructing
your questionnaire form to be opened, and filtered to only
show the questionnaire that matches the ID of the one you
clicked on. Even if your questionnaire form's
recordsource looks at all of the questionnaires (SELECT *
FROM tblQuestionnaires), this OpenForm method will only
show the one you want.

Private Sub cmdNewQ_Click()

Dim lngClient as Long

lngClient = me.ClientID
DoCmd.OpenForm "frmClientQuestionnaires",
acNormal, , , acFormAdd
Forms!frmClientQuestionnaires.ClientNo =

End Sub

This code passes the ID of your client to the variable,
then opens the same form, but puts it into data entry mode
so you will only be adding a new questionnaire and not
typing over an existing one. Once the form opens, it
passes the clientID to the control (field) on the form
that represents the foreign key field ClientID (from the
Questionnaires table). Once this code has finished,
you'll be looking at a blank questionnaire with the
exception of the clientID which has been set for you.

Good luck.
 
thank you so much for all your help, i'm going to the
library after work for a book. is this something that can
be self taught or taking a class is more appropriate??
-----Original Message-----
I would recommend a book on VBA. Remember, you're dealing
with Access, which uses Visual Basic for Applications (or
VBA), not Visual Basic. While they are basically the same
language, VBA is actually a subset of VB, and a book on
Access and VBA would be helpful.

Here's a highlight.

Private Sub lstMyListBox_Click()

Dim lngQuestionnaire as Long
Dim strSQL as String

lngQuestionnaire = lstMyListBox.Column(0)
strSQL = "[QuestionnaireID] = " & lngClientQ
DoCmd.OpenForm "frmClientQuestionnaires", acNormal,,
strSQL

End Sub

That code passes the value from the questionnaire you
clicked on (the column(0) would be the first column of the
listbox's RowSource property, the questionnaire's ID) to
the variable. Then it passes a SQL string to a string
variable that incorporates that questionnaire's ID
number. Then it calls the OpenForm method, instructing
your questionnaire form to be opened, and filtered to only
show the questionnaire that matches the ID of the one you
clicked on. Even if your questionnaire form's
recordsource looks at all of the questionnaires (SELECT *
FROM tblQuestionnaires), this OpenForm method will only
show the one you want.

Private Sub cmdNewQ_Click()

Dim lngClient as Long

lngClient = me.ClientID
DoCmd.OpenForm "frmClientQuestionnaires",
acNormal, , , acFormAdd
Forms!frmClientQuestionnaires.ClientNo =

End Sub

This code passes the ID of your client to the variable,
then opens the same form, but puts it into data entry mode
so you will only be adding a new questionnaire and not
typing over an existing one. Once the form opens, it
passes the clientID to the control (field) on the form
that represents the foreign key field ClientID (from the
Questionnaires table). Once this code has finished,
you'll be looking at a blank questionnaire with the
exception of the clientID which has been set for you.

Good luck.
-----Original Message-----
that makes sense on having a list box for all
questionaires on the specific client and a cmd button for
a new questionaire, NOW, the question is, what are the
codes to do that, is there a book or website that i can
learn more about codes, learn what openargs and all that
means, cuz i really dont thanks for all your help be
a
Click to expand...
create
a form
is
.
Click to expand...
.
Click to expand...
 
Back
Top