At Wits End

[Guest]

I'm slowly going out of my mind with Access and VBA. As those of you who have
offered your help before know, I have programmed in COBOL and C, but have not
been able to quite grasp the VBA syntax. I have a form that is used to enter
information on yard sales for a city. There is a button on the form that
opens another form that is used to enter family members that reside at the
same as address as the person having the sale. I can't seem to figure out how
to pull up the family member records for the person entered in the initial
form. I've checked the values before I use the docmd and the link criteria
string has the right values. However, when I check the form that's called to
be opened, the values of the fields in that table are not set. I now realize
that that's not going to happen since it's being passed as a 'where'
statement without the 'where'. How to I get the values from one form to the
other? I think if I could ever grasp this, it would help to flip on the light
for VBA and Access. The table for the initial form is:

CustomerID - PK
FirstName
LastName
Address
PhoneNumber
The table for the form being called is:

FamiliyMembersID - PK
CustomerID
CustomerAddress
RelationsName
Relationship

If anyone could help switch on the light, I would be very appreciative.
Thanks for everyone's help in the past and for any help in the future.

RandyM

 

[Guest]

I'm slowly going out of my mind with Access and VBA

You're doing pretty good, most people rapidly go out of their mind doing
Access and VBA...

 

[Douglas J Steele]

When you use the DoCmd.OpenForm method, you have the option to pass an
OpenArgs parameter. This can be anything you want.

In the Open event of the other form, you can check whether anything was
passed as an OpenArgs using:

If IsNull(Me.OpenArgs) Then
' nothing was passed
Else
' something was passed
End If

 

[Guest]

hi, if you could put the code you have used to open the family members form
and the linkcriteria i might be able to suggest something simple.

rhys

 

[Guest]

I may not be grasping your problem, but this is what I'm coming up with:

On your main form, either visible or not, have a field txtCustomerID. Then
base the DataSource, of your second form with the criteria of Having
CustomerID = [Forms]![frmFirstForm]![txtCustomerID].

This should open the second form with the related family members.

HTH

Sharkbyte

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "OpenArgs.mdb" which illustrates the advice that Doug gave.
(Sometimes it's nice to have a sample to pull apart.)

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tony Toews]

"Marvin P. Winterbottom"

You're doing pretty good, most people rapidly go out of their mind doing
Access and VBA...

Hey, speak for yourself.

NO, YOU"RE NOT SPEAKING FOR ME.

Arrrgghhhh.

Tony <smile>
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Albert D.Kallal]

The code is only ONE line of code...

(stuff like this is why ms-access is SOOO much easer then general packages
like VB).

The code needed to open the 2nd form is

me.Refresh ' this forces a disk write
docmd.OpenForm "frmFamilyMembers",,,"CustomerID = " & me!customerID

now, the above will open the 2nd form..and display ONLY the family members.
However, this will NOT solve the problem of when you add new records to the
2nd form...as how will it know they belong to a particular customer? (you
have to code this yourself, OR USE A SUB-FORM).

By the way, any particular reason why you don't consider a sub-form.
Sub-forms in ms-access are for those classic one-to many relationships.

sub-forms will automatically SET the value of customerID in the family
members form....

If you use separate forms, then of course there is no way for the form to
know what customer the family member belongs to (you have then code this
problem).

So, using a sub-form means you can do this without having to write any
code...

You can read about sub-forms here

http://www.members.shaw.ca/AlbertKallal/Articles/fog0000000005.html

Now, back to our problem of when you add records in the 2nd form, how do you
"add", or "setup the customer ID?

Well, the best approach really depends on if you will ever allow the
frmFamily members to be launched without first launching the frmCustomers

if you *always* are going to work this way, then simply put in the
frmFamilyMebers "before insert" event...

me!CustomerID = forms!frmCustomer!CustomerID


That is all you need.....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Guest]

Rhys,
This is the code for the command button. I'm not sure if it's right or not.
I copied it from another procedure that did something similar and tried to
adapt it to this.
Private Sub cmdFamilyMembers_Click()
On Error GoTo Err_cmdFamilyMembers_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim blnCheck As Boolean
binCheck = IsNull(Customer)

I don't really have any code in the form that's being opened because I never
could find a value there. I put a procedure that had a 'stop' in it in the
Open Form event and just checked some variables there, but evidently I didn't
check the right ones. Anyhoo, this is what I have, any help or suggestions I
am very grateful for.

RandyM
If binCheck = True Then
strLinkCriteria = "[tblFamilyMembers.CustomerID] = " & Me!CustomerID
& _
" AND [tblFamilyMembers.Address] = " & Me![Address]
Else
strLinkCriteria = "[tblFamilyMembers.CustomerID] = " & _
Str(Nz(Me![Customer], 0)) & " AND [tblFamilyMembers.Address] = " &
Me![Address]
End If
stDocName = "frmFamilyMembersSubForm"

DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdFamilyMembers_Click:
Exit Sub

Err_cmdFamilyMembers_Click:
MsgBox Err.Description
Resume Exit_cmdFamilyMembers_Click

End Sub

 

[Guest]

Albert,
The main reason I didn't use a sub-form for this is I'm already using a
sub-form for sales events. In the city you have to have a license for yard
sales, garage sales, estate sales, etc, and there is already a sub-form on
the main form for this. I felt that using another would clutter things up, so
I opted for opening the other form with a button. Looking back the clutter
might not be such a bad thing.
Thanks,
RandyM

 

[Guest]

Douglas,
Can you use anything in the OpenArgs parameter? The reason I ask is that
the primary key in the family members table is just an autonumber field. The
record for the family members will have to be found using the CustomerID
field (foreign key) and the CustomerAddress in the family members table. This
is in case the Customer moves or has multiple addresses. This might not be
the right way to do it, but as I've said on more than one occasion...I'm a
newbie at the Access and the VBA and am slogging through as best as I can.
I'm sure down the road after the proverbial light comes on I'll look back at
some of the ways I did things and go "Why did I do that!"
Randy M

 

[Douglas J Steele]

You can use anything you want in the OpenArgs parameter.

In fact, it's not uncommon to concatenate multiple values together and pass
them as the OpenArgs, then split them back in the next form.

Did you take a look at the example Roger suggested? His sample databases are
usually very useful.

 

[Guest]

Roger,
Thanks for the OpenArgs.mdb code. I downloaded it and have been looking
thru it. I'm beginning to see how to use it. I've had some programming
experience with C back years ago and so VBA, is not completely alien to me,
just mainly the syntax. I understand the logic, but another problem I'm
having I think is not starting out at the 'top' of the logic chain so to
speak. In C when I coded, it was always from scratch so I knew how the logic
flowed and could track and debug easier. It seems with Access and VBA, I
don't always know where I am within the overall flow of the program. It looks
like it just jumps in here and there and guess that's from the event
driven-ness of it. I'm gradually adapting to this erratic logic flow-stream,
at least to me right now it feels erratic, and with yours and the others here
who have been so helpful, I know I'm gonna eventually see the light. Thanks
again for turning me on to that database program, I think it's gonna help a
lot.
RandyM

 

[Tony Toews]

The main reason I didn't use a sub-form for this is I'm already using a
sub-form for sales events. In the city you have to have a license for yard
sales, garage sales, estate sales, etc, and there is already a sub-form on
the main form for this. I felt that using another would clutter things up, so
I opted for opening the other form with a button. Looking back the clutter
might not be such a bad thing.

Consider using subforms on different pages of the tab control. I do
this all the time and it works very well for organizing the clutter.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Roger Carlson]

Randy,

I understand your confusion. Before coming to Access I programmed in
Fortran, COBOL, BASIC, Pascal, and C (all pre-Windows). It's not really all
that different.

For instance, in a C program, I would usually open the program with some
sort of Menu screen. The user would tab between menu items and hit enter on
the one they wanted. Then the program would branch to a subroutine for that
function, etc. If you think of your form as a very complex menu screen that
has program code behind it, it works out to be the same thing. Each event
is simply a call to a subroutine either behind the form itself or a global
routine in one of the modules.

All they've really done is make the menu (form) easier for the user to use
and you don't have to program it.

Hope this helps some. Keep at it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Brian Bastl]

Another poster is having problems with a downloaded sample from your
website. Maybe you can step in there.

Brian

message below:

 

[Roger Carlson]

Thanks, she contacted me off-line

--
--Roger Carlson
MS Access MVP
www.rogersaccesslibrary.com

Another poster is having problems with a downloaded sample from your
website. Maybe you can step in there.

Brian

message below:

My form has an event procedure in the beforeUpdate as seen below... I

have

a
combobox on the same main form that has afterUpdate event. the thing is that
when I select the AcctDescrAvailable combox to select Yes/No, I have popup
that says select an account name which comes from the form's beforeupdate
procedure. So I press ok then then the runtime error 2501, the Domenuitem
action was cancelled.
If ypu press debug it points to the
DoCmd.DoMenuItem acFormBar, acEditMenu, acPaste, , acMenuVer70
Which is in the afterupdate procedure of that combobox ...

Any Help with this would be highly appreaciated....

***************beforeUpdate Procedure******************
If Me!AcctDecision.Column(1) = "Pending" Then
Debug.Print "Pending"
If IsNull(Me!AcctCode) Then
Debug.Print "AcctCode is null. Cancelling."
Cancel = True
strControl = strControl & "Select an Account Name" & vbCrLf
End If
End If
******************************************************

********************************************************
Private Sub AcctDescrAvailable_AfterUpdate()
Dim strARG As String
Dim strSQL As String
Dim DocName As String
Dim LinkCriteria As String
Dim Response As Integer
Dim Cancel As Boolean

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

If AcctDescrAvailable.Value = "No" Then
strARG = _
"Account Information, SELECT MailAddressLine1 as
AcctAddrLine1,MailAddressLine2 as AcctAddrLine2, propCity as AcctCity,
MailState as AcctState,MailZipCode as AcctZipCode FROM tblAcctInfor where
[AcctID] = " & AcctID & ";"
DocName = "frmAcctDescInput"
DoCmd.OpenForm DocName, , , LinkCriteria, , , strARG
Else
strSQL = "UPDATE tblAcctInfor SET MailAddressLine1, MailAddressLine2,
MailCity, MailState, MailZipCode= null where [AcctID] =" & AcctID & ";"
CurrentDb.Execute strSQL
End If

End Sub


***********************************************
The idea in this form is reffereing to the idea that I found in the
following website

http://www.rogersaccesslibrary.com/download3.asp?SampleName=OtherProblem.mdb