Excel for Life: 25 new messages in 14 topics

microsoft.public.access.modulesdaovba
http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

microsoft.public.access.modulesdaovba@googlegroups.com

Today's topics:

==============================================================================
TOPIC: Table records are being deleted for unknown reason
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/85263686916f4e45?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 12:39 am
From: Bruce

I have a MSAccess 2003 database which is losing records from one of its
tables (i.e. they are there 1 day but not the next).

The user who maintains the data says they are not deleting the records and
they are not getting any error messages. When they run reports they notice
items are missing from time to time. Also, there is only 1 user who acessess
this database.

The database is hosted on a remote server on an intranet and has an average
to slow response time (depending on traffic), even so the database is small
under 2mb.

Anyone come across anything like this before?
What are some options to diagnose and solve the problem?

Bruce

==============================================================================
TOPIC: Waiting for input from a form
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f3c5d8c996864ce6?hl=en
==============================================================================

== 1 of 5 ==
Date: Wed, Nov 14 2007 1:55 am
From: Rod

Half-way through my code, the user needs to supply information from a
combo-box.

How do I pause my code while the user makes their selection? I cannot use
InputBox or MsgBox, since I need the combobox object. How do I open an
ordinary form and then wait for the user to press "OK" (or whatever button I
create) before continuing to execute the code? Setting the Modal property to
Yes doesn't help.

Thanks

== 2 of 5 ==
Date: Wed, Nov 14 2007 2:07 am
From: "Pieter Wijnen"

Something Like

DoCmd.OpenForm "MyForm"

While FisLoaded("MyForm")
DoEvents
Wend

Use your own favourite FisLoaded (thers's one at http://www.mvps.org/access)

HtH

Pieter

"Rod" <Rod@discussions.microsoft.com> wrote in message
news:107E9CE0-20E5-4575-8321-57B924622E3E@microsoft.com...
> Half-way through my code, the user needs to supply information from a
> combo-box.
>
> How do I pause my code while the user makes their selection? I cannot use
> InputBox or MsgBox, since I need the combobox object. How do I open an
> ordinary form and then wait for the user to press "OK" (or whatever button
> I
> create) before continuing to execute the code? Setting the Modal property
> to
> Yes doesn't help.
>
> Thanks

== 3 of 5 ==
Date: Wed, Nov 14 2007 4:39 am
From: "Douglas J. Steele"

Why not open the form using acDialog?

DoCmd.OpenForm "MyForm", WindowMode:=acDialog

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Pieter Wijnen"
<it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
wrote in message news:e7YC3XqJIHA.4880@TK2MSFTNGP03.phx.gbl...
> Something Like
>
> DoCmd.OpenForm "MyForm"
>
> While FisLoaded("MyForm")
> DoEvents
> Wend
>
> Use your own favourite FisLoaded (thers's one at
> http://www.mvps.org/access)
>
> HtH
>
> Pieter
>
>
> "Rod" <Rod@discussions.microsoft.com> wrote in message
> news:107E9CE0-20E5-4575-8321-57B924622E3E@microsoft.com...
>> Half-way through my code, the user needs to supply information from a
>> combo-box.
>>
>> How do I pause my code while the user makes their selection? I cannot use
>> InputBox or MsgBox, since I need the combobox object. How do I open an
>> ordinary form and then wait for the user to press "OK" (or whatever
>> button I
>> create) before continuing to execute the code? Setting the Modal property
>> to
>> Yes doesn't help.
>>
>> Thanks
>
>

== 4 of 5 ==
Date: Wed, Nov 14 2007 4:45 am
From: "John Spencer"

Can't you get the value before you start the code and just pass it in as an
argument?

If for some reason the answer is no, then try this technique.
Open the form in acDialog mode.
Hide the form when your user has input data (code the ok button to set the
form's visible property to false.
Check if the form is open and if so grab the value(s) you need
Close the form

.... Your code

'Stop processing in current procedure until form closes or is no longer
visible
DoCmd.OpenForm "YourFormname", windowmode:=acdialog
If isFormLoaded = True Then
someVariable = Forms!YourFormName!YourControlName
DoCmd.Close acForm, "YourFormname"
End if

... resume processing your code

'Sample code to check if form is loaded (works for all versions of Access up
to 2003 and probably 2007 (not tested)

Function IsFormLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet
view.

Const conObjStateClosed = 0
Const conDesignView = 0

If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsFormLoaded = True
End If
End If

End Function
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

== 5 of 5 ==
Date: Wed, Nov 14 2007 5:18 am
From: "Pieter Wijnen"

Since he said that modal mode didn't work <g>
Can happen when timers are involved

Pieter

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:uqQOhtrJIHA.4272@TK2MSFTNGP05.phx.gbl...
> Why not open the form using acDialog?
>
> DoCmd.OpenForm "MyForm", WindowMode:=acDialog
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Pieter Wijnen"
> <it.isi.llegal.to.send.unsollicited.mail.wijnen.nospam.please@online.replace.with.norway>
> wrote in message news:e7YC3XqJIHA.4880@TK2MSFTNGP03.phx.gbl...
>> Something Like
>>
>> DoCmd.OpenForm "MyForm"
>>
>> While FisLoaded("MyForm")
>> DoEvents
>> Wend
>>
>> Use your own favourite FisLoaded (thers's one at
>> http://www.mvps.org/access)
>>
>> HtH
>>
>> Pieter
>>
>>
>> "Rod" <Rod@discussions.microsoft.com> wrote in message
>> news:107E9CE0-20E5-4575-8321-57B924622E3E@microsoft.com...
>>> Half-way through my code, the user needs to supply information from a
>>> combo-box.
>>>
>>> How do I pause my code while the user makes their selection? I cannot
>>> use
>>> InputBox or MsgBox, since I need the combobox object. How do I open an
>>> ordinary form and then wait for the user to press "OK" (or whatever
>>> button I
>>> create) before continuing to execute the code? Setting the Modal
>>> property to
>>> Yes doesn't help.
>>>
>>> Thanks
>>
>>
>
>

==============================================================================
TOPIC: Access 2000 mdb VBA and Concurrency
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/1b482beb6ceb76a5?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 3:14 am
From: ruggaball

I appologize, The problem is not concurrency, a user typed apostrophe in a
textbox, so an SQL update derived from data in the textbox corrupted. used
event Keypressed and then cancel. Thankyou

==============================================================================
TOPIC: Update query
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/f6ee0eb0b02ba92f?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 4:32 am
From: "John Spencer"

It looks to me as if you just need an additional table with 12 records
TblChanges
Afid (Primary Key)
NewCustomerID

The following query may work for you - or it may not be updatable.
BEFORE you try this I would make a backup of my data and then check the
results.

UPDATE (Customers INNER JOIN TblChanges
ON Customers.Afid = tblChanges.Afid)
INNER JOIN Orders1
ON Customer.CustomerID = Orders1.CustomerID
SET Orders1.CustomerID = [tblChanges].[NewCustomerID]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

"samotek via AccessMonster.com" <u15330@uwe> wrote in message
news:7b2f8af33156e@uwe...
>I want to write a function looking into a query :
> SELECT Customers.afid, orders1.customerid
> FROM Customers INNER JOIN orders1 ON Customers.Customerid = orders1.
> customerid;
>
> With the following condition :
> If customers.afid = 3 , to update the order1.cusomerid to 124
>
> Like that :
> UPDATE Customers INNER JOIN orders1 ON Customers.Customerid = orders1.
> customerid SET orders1.customerid = 124
> WHERE (((Customers.afid)=3));
>
> Also, when customers.afid = 4, to update order1.customerid to 320
> I have 12 cases of the afid,so perhaps code is better
> Etc etc
>
> Can you help ?
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1
>

==============================================================================
TOPIC: Send Object question
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a86de0a1b9376945?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 4:49 am
From: Bongard

Hi, I am trying to use a combo box in a form for the user to to choose
a [LoanServicer]. Once this is done, they will click a button that is
linked to a send object macro. Each Loan Servicer is responsible for
sending an email with with an attachment to each correspondent. For
instance, the name Brian is chosen on the form the macro should run
the qry [Correspondent Details] for each correspondent where the
[LoanServicer] field = Brian. For each correspondent, the query should
then be attached in an email and sent to that correspondent. The
correspondents email address is the field [PrimaryEmail] from the
Correspondent Details query.

I assume that I will have to create some sort of a looping statement
but I am not sure how to get started. Any help that someone could
offer would be much appreciated.

Thanks much,
Brian

==============================================================================
TOPIC: Compile error
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0635a4971cf07a70?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 5:02 am
From: "alecwood via AccessMonster.com"

If you take the brackets off on that line thus

adb.OpenCurrentDatabase strDbName, , StrPassword

this should work. Dunno why though since the VBA help on OpenCurrentDatabase
shows the brackets being used, but anyway, it worked for me without them,
though I did define the Exclusive parameter as False, thus

adb.OpenCurrentDatabase strDbName, False, StrPassword

Alec

samotek wrote:
>n my function listed below i get compile error in the following line :
>adb.OpenCurrentDatabase (strDbName,,strPassword)
>can you help ?
>
>Private Function KillObject(strDbName As String, acObjectType As Long,
>strObjectName As String, StrPassword As String)
>'Call KillObject(GPath, 0, "products")
>Dim StrPassword As String
>StrPassword = "secret"
>Dim db As DAO.Database
>Dim adb As Object
>Set adb = CreateObject("Access.Application")
>adb.OpenCurrentDatabase (strDbName,,strPassword)
>adb.DoCmd.DeleteObject acObjectType, strObjectName
>adb.CloseCurrentDatabase
>Set adb = Nothing
>End Function

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

==============================================================================
TOPIC: Adding Records
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/e7ad8e5914a3034a?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 5:57 am
From: Martin.0505

Denise,

Let's try next.

I assume you have txtboxes at your form where dta was entered!
Give these textboxes a decent name (select box, then in properties: tab
"other", field "Name") like "txtStartdate" for a startdate or "txtAmount" for
a number of items. Give it a logicalname.
Then create a button on your form to save the data.
Click on the right mousebutton while selecting the new button and select
"Build Event" to go the codepage.
the code to write ,looks like following, depending of the fields in your
table you need to add and add the real table name at "TblName".

Private Sub pbSaveRecord_Click()
Dim rsDestinationTable As Recordset

Set rsDestinationTable = CurrentDb.OpenRecordset("TblName", dbOpenTable,
dbFailOnError)

With rsDestinationTable
.AddNew
.txtStartdate = Me.txtStartdate
.txtAmount = Me.txtAmount
.Update
End With

End Sub

Goodluck,
Martin

"John Spencer" wrote:

> You can use VBA to execute an append query. You can build the append
> query string in VBA.
>
> If it is one record based on some values that you know then the SQL might be
>
> StrSQL = "INSERT INTO [TheTable] " & _
> "([numberFieldA], [textFieldB], [DateFieldC])" & _
> " Values (" & intSomeNumber & _
> ", """ & strSomeText & """, #" & _
> txtSomeDateValue & "#)"
>
> CurrentDB().Execute StrSQL
>
> THat is the best I can do without a lot more detail.
>
> '====================================================
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> '====================================================
>
>
> denise wrote:
> > I have a form that is not based on the table that I want to add a record to.
> > Isn't there a way in VB to open a table, append a record to it, then close
> > the table?
> >
> > "John Spencer" wrote:
> >
> >> I'm sorry. I'm being dense. I still don't understand what you want to do.
> >>
> >> Anyone else care to step in?
> >>
> >> --
> >> John Spencer
> >> Access MVP 2002-2005, 2007
> >> Center for Health Program Development and Management
> >> University of Maryland Baltimore County
> >> ..
> >>
> >> "denise" <denise@discussions.microsoft.com> wrote in message
> >> news:9A7044E0-732C-4C4E-9A8D-B2F87C04225F@microsoft.com...
> >>> The table is in the same database that I have a form opened in. No the
> >>> form
> >>> is not based on the table.
> >>>
> >>> I know that there has to be a way in VB to open the table and add a
> >>> record,
> >>> I just don't know how to do it.
> >>>
> >>> "John Spencer" wrote:
> >>>
> >>>> Could you explain a little more abut what you want to do?
> >>>>
> >>>> A table that isn't opened could mean a lot of things.
> >>>> The database isn't open
> >>>> The table is in another database and is not a linked table
> >>>> You have a form open and it doesn't have the table in its record source
> >>>> You have a query open and the table is not included in the tables used by
> >>>> the query
> >>>> ....
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> John Spencer
> >>>> Access MVP 2002-2005, 2007
> >>>> Center for Health Program Development and Management
> >>>> University of Maryland Baltimore County
> >>>> ..
> >>>>
> >>>> "denise" <denise@discussions.microsoft.com> wrote in message
> >>>> news:0E6DA2FC-7FB6-499E-8FF1-B5D3FED9318C@microsoft.com...
> >>>>> How can I add records to a table that isn't opened?
> >>>>
> >>>>
> >>
> >>
>

==============================================================================
TOPIC: Insert large base64 into memo field
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/0b33175be7d352d9?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 6:09 am
From: "Gary Walter"

"Mike Iacovou"wrote:
> starting out in access... i would like to store & retrieve a large
> (base64)
> string in an access table field... i understand that it is possible to
> store
> 1Gb data programmatically (or at least 64K) via VBA...
>
> If anyone has the time, please could you:
>
> 1. confirm that memo fields can store > 64K in Acess 2000+
> 2. illustrate the correct method to store & retrieve a large string in a
> memo field - for example field 'Base64' in table 'BinaryData' - this table
> only has this single field.
>
> Constructing an INSERT statement and executing it seems to limit entry to
> 64K...
>
Hi Mike,

Yes (at least in Access 97-2002) you can store a string > 64K
in a memo field.

Open a recordset and either AddNew or Edit the recordset field

something like

Dim rs AS DAO.RecordSet

Set rs = CurrentDb.OpenRecordset("BinaryData")

With rs
.AddNew
!Base64 = strYourBigString
.Update
End With

So...it can be done.

You should be able to get the big string back
with a query as long as you do not Group By
(or sort on?) the Base64 field which will truncate
the field to 255 chars (256?).

I cannot help but ask if this is the "best way."

In a form textbox, you won't be able to edit it
(and probably not even scroll through it).

I am not familiar with Base64 strings, but is there
something equivalent to LF or CR. For example,
in one project I worked on, each "line" of a big blob
of characters appeared to end in "%0d%0a"

If that were the case, I might parse your big string
into "lines" for each record, then concatenate these
lines back together when you need the full string.

If these "lines" turned out to be less than 256 chars,
then you could use type Text which could also help you
with "tendency" of memo fields to corrupt (that has
never happened to me, but some here might say "yet").

Then, maybe that is irrelevant to your purposes...

good luck,

gary

==============================================================================
TOPIC: Please help me reguarding this matter. Thank You.
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/a5ab286e9847cfff?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 6:14 am
From: lasithakamalpriya@gmail.com

Please any one help me with Ms Access 2002
I want create a data table In which should be One Date & Many Items
I don't want to enter the date Recordset by Recordset it is wasting
the time
Please tell me How I can do It. Just like using sub form. If you have
any solution please send to my email address which is
lasithakamalpriya@google.com

==============================================================================
TOPIC: Calling Module function in query
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/5cdae39d26228303?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 6:17 am
From: Klatuu

No problem, just wanted the OP to be aware.
--
Dave Hargis, Microsoft Access MVP

"Brian" wrote:

> Good point. That's what I get for posting a response in the middle of the
> night.
>
> "Klatuu" wrote:
>
> > Brian, I believe the way you have it coded it will choke on a circular
> > reference.
> > It is the Slab as Slab part:
> > SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
> >
> > It may be necessary to use a different name:
> > SELECT Incentive.SlbValue, Slab([SlbValue]) As SlabType from Incentive
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Brian" wrote:
> >
> > > Try this:
> > >
> > > Make sure your Function is Public, like this:
> > >
> > > Public Function Slab(SlabVal As Integer) As Integer
> > > If SlabVal > 0 And SlabVal <= 20 Then
> > > Slab = 1
> > > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > > Slab = 2
> > > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > > Slab = 3
> > > ElseIf SlabVal > 100 Then
> > > Slab = 4
> > > End If
> > > End Function
> > >
> > > Now, select like this:
> > >
> > > SELECT Incentive.SlbValue, Slab([SlbValue]) As Slab from Incentive
> > >
> > > In the query builder, the Slab field will look like this:
> > >
> > > Slab:Slab([SlbValue])
> > >
> > > In addition, if SlbValue could ever be null, the function variables will
> > > need to be Variant types instead of Integers, and you will need to handle the
> > > null value somewhere in the If...ElseIf statement.
> > >
> > > "Hitesh Chauhan" wrote:
> > >
> > > > Hi,
> > > > I am having a user defined function in module. i want to call in my select
> > > > statement. is it possible. if yes How?
> > > >
> > > > Function Slab(SlabVal As Integer)
> > > > If SlabVal > 0 And SlabVal <= 20 Then
> > > > Slab = 1
> > > > ElseIf SlabVal > 20 And SlabVal <= 50 Then
> > > > Slab = 2
> > > > ElseIf SlabVal > 50 And SlabVal <= 100 Then
> > > > Slab = 3
> > > > ElseIf SlabVal > 100 Then
> > > > Slab = 4
> > > > End If
> > > > End Function
> > > >
> > > > the above function i want to call in select statement like
> > > >
> > > > select SlbValue,slab(slbvalue) from Incentive
> > > >
> > > >
> > > > regards,
> > > > hiteshc

==============================================================================
TOPIC: data entry mode subform
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/8f1b7e46e8e3b5cc?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 6:20 am
From: denise

When I requery a list box on the subform it takes the subform out of
DataEntry mode. It then displays the first record from the table in the data
entry fields that I have set up. Any ideas as to why it would change the
mode from DataEntry=Yes to No?

==============================================================================
TOPIC: conting by letter rather than number
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/d5ff87184daf9c06?hl=en
==============================================================================

== 1 of 1 ==
Date: Wed, Nov 14 2007 6:25 am
From: Klatuu

I can show you how to write the code, but I need to know some rules.
How many characters can be in the string?
What is the order. For example, I would expect it would go from
A - Z
AA - AZ
AB - ABZ

In your original example, you show going form BB to CC, but that doesn't
seem logical. However, it is your app and your rules, so I need to know how
the order actually is.
--
Dave Hargis, Microsoft Access MVP

"ArielZusya" wrote:

> OK... so... that's so cool... quick question though... How do I get past 90?
> 91 seems to go to [ rather than to AA? I imagine that's because asc must be
> returning the key mapped to that value or something like that, right? I
> suppose I would have to have it do some math or something to get it to
> concatonate the chr of two asc returns or something. I noticed that asc("AA")
> returns 65 so I'm not clear on how to make this all work. Do I need to loop
> this? Wouldn't it have been great if the two groups both used something
> easier like GroupA1, GroupA2, GroupA3, etc. and GroupB1, GroupB2, GroupB3
> etc. That I could do with no assistance. At least I'm learning something
> new though. Any thoughts on what I should do next would be greatly
> appreciated! Thanks for all your help.
>
> "ArielZusya" wrote:
>
> > I'm working on a dataset which numbers each entry by number (1, 2, 3, etc.)
> > for one group and by letter for another group (A, B, C, ... AA, BB, CC, ...
> > AAA, BBB, CCC, etc.). I'd like to have the system figure out the last record
> > entered and detect the letters and know what should come next. That way if
> > the 28th record is entered (BB) it knows that the 29th record should be CC.
> > Is there an easy way to accomplish this using VBA? I could simply create a
> > table with the first 130 entries (A - ZZZZZ) but I'd rather have the system
> > work a bit more independantly (so in the event that there is 131 records I
> > don't run into trouble). Thanks!

==============================================================================
TOPIC: Combining two tables into a query to make a report that displays o
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/aef8f6b1a7a79fc8?hl=en
==============================================================================

== 1 of 4 ==
Date: Wed, Nov 14 2007 6:27 am
From: matt

I have a database that has a main table and sub-table. I am trying to create
a report that will display the current record and the related info on the
sub-form. I was trying to use a SQL/Query. Any help thanks.

== 2 of 4 ==
Date: Wed, Nov 14 2007 7:04 am
From: "Jeff Boyce"

Matt

Create a new query. Add both tables. Join them on their related field(s).
Add fields from both that you want in the report. Save and close the query.

Create a new report. For a source, point at the query you just created.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"matt" <matt@discussions.microsoft.com> wrote in message
news:B0D0F42D-C325-42C7-BAEE-16D381C0C6CB@microsoft.com...
> I have a database that has a main table and sub-table. I am trying to
create
> a report that will display the current record and the related info on the
> sub-form. I was trying to use a SQL/Query. Any help thanks.

== 3 of 4 ==
Date: Wed, Nov 14 2007 7:11 am
From: Klatuu

Access will do that for you.
The Record Source of the main form should be the main table.
The Record Source of the sub form should be the child table.

Sub forms are contained in a subform control. Two properties of the subform
control are Link Master Fields and Link Child Fields. Put the name of the
field in the main table that links to the child time in the Link Master
Fields. Put the name of the field in the child table that links the child to
the master in the Link Child Fields.
--
Dave Hargis, Microsoft Access MVP

"matt" wrote:

> I have a database that has a main table and sub-table. I am trying to create
> a report that will display the current record and the related info on the
> sub-form. I was trying to use a SQL/Query. Any help thanks.

== 4 of 4 ==
Date: Wed, Nov 14 2007 7:32 am
From: matt

Thanks. I have my Main form set up. Like that but now i am trying to creat a
button on my form that will display a report based on that current record
pulling from both tables.

"Klatuu" wrote:

> Access will do that for you.
> The Record Source of the main form should be the main table.
> The Record Source of the sub form should be the child table.
>
> Sub forms are contained in a subform control. Two properties of the subform
> control are Link Master Fields and Link Child Fields. Put the name of the
> field in the main table that links to the child time in the Link Master
> Fields. Put the name of the field in the child table that links the child to
> the master in the Link Child Fields.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "matt" wrote:
>
> > I have a database that has a main table and sub-table. I am trying to create
> > a report that will display the current record and the related info on the
> > sub-form. I was trying to use a SQL/Query. Any help thanks.

==============================================================================
TOPIC: DSum
http://groups.google.com/group/microsoft.public.access.modulesdaovba/browse_thread/thread/401d62a0037f9850?hl=en
==============================================================================

== 1 of 5 ==
Date: Wed, Nov 14 2007 6:46 am
From: K

Good day.

I am trying to and populate a field in the main form "TTLCostCND" by
summing a field in a subform"CostFT. I get the error message Argument not
optional. Any help would be appreciated to achieve this goal

Mainform is called "frmQuotes" - Table is called tblQoutes
Subform is called "frmQuotesSubform" - Table is called tblQuotesSubfrm

Private Sub UOM_AfterUpdate()
Me.CostFT = Round(Me.CCRATE * Me.HRSCft / 100, 4)
Me.Form_frmQuotes!TTLCostCND = DSum(Me.CostFT)

End Sub

Thanks in advance.

== 2 of 5 ==
Date: Wed, Nov 14 2007 7:09 am
From: Klatuu

What you are calling a field is a control. It only contains one value, so it
can't be summed. The DSum is a domain aggragate function, meaning it is used
with tables or queries to sum a field.
--
Dave Hargis, Microsoft Access MVP

"K" wrote:

>
> Good day.
>
> I am trying to and populate a field in the main form "TTLCostCND" by
> summing a field in a subform"CostFT. I get the error message Argument not
> optional. Any help would be appreciated to achieve this goal
>
> Mainform is called "frmQuotes" - Table is called tblQoutes
> Subform is called "frmQuotesSubform" - Table is called tblQuotesSubfrm
>
> Private Sub UOM_AfterUpdate()
> Me.CostFT = Round(Me.CCRATE * Me.HRSCft / 100, 4)
> Me.Form_frmQuotes!TTLCostCND = DSum(Me.CostFT)
>
> End Sub
>
> Thanks in advance.

== 3 of 5 ==
Date: Wed, Nov 14 2007 7:16 am
From: K

Thanks for your time. Is there a way to complete this task?

"Klatuu" wrote:

> What you are calling a field is a control. It only contains one value, so it
> can't be summed. The DSum is a domain aggragate function, meaning it is used
> with tables or queries to sum a field.
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "K" wrote:
>
> >
> > Good day.
> >
> > I am trying to and populate a field in the main form "TTLCostCND" by
> > summing a field in a subform"CostFT. I get the error message Argument not
> > optional. Any help would be appreciated to achieve this goal
> >
> > Mainform is called "frmQuotes" - Table is called tblQoutes
> > Subform is called "frmQuotesSubform" - Table is called tblQuotesSubfrm
> >
> > Private Sub UOM_AfterUpdate()
> > Me.CostFT = Round(Me.CCRATE * Me.HRSCft / 100, 4)
> > Me.Form_frmQuotes!TTLCostCND = DSum(Me.CostFT)
> >
> > End Sub
> >
> > Thanks in advance.

== 4 of 5 ==
Date: Wed, Nov 14 2007 7:19 am
From: Klatuu

What is it you are trying to Sum?
--
Dave Hargis, Microsoft Access MVP

"K" wrote:

> Thanks for your time. Is there a way to complete this task?
>
> "Klatuu" wrote:
>
> > What you are calling a field is a control. It only contains one value, so it
> > can't be summed. The DSum is a domain aggragate function, meaning it is used
> > with tables or queries to sum a field.
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "K" wrote:
> >
> > >
> > > Good day.
> > >
> > > I am trying to and populate a field in the main form "TTLCostCND" by
> > > summing a field in a subform"CostFT. I get the error message Argument not
> > > optional. Any help would be appreciated to achieve this goal
> > >
> > > Mainform is called "frmQuotes" - Table is called tblQoutes
> > > Subform is called "frmQuotesSubform" - Table is called tblQuotesSubfrm
> > >
> > > Private Sub UOM_AfterUpdate()
> > > Me.CostFT = Round(Me.CCRATE * Me.HRSCft / 100, 4)
> > > Me.Form_frmQuotes!TTLCostCND = DSum(Me.CostFT)
> > >
> > > End Sub
> > >
> > > Thanks in advance.

== 5 of 5 ==
Date: Wed, Nov 14 2007 7:24 am
From: K

I mam trying to sum the control called [CostFT] in a subform called
"FrmQuotesSubform" and then display the Sum of [CostFT] in a control called
TTLCOSTCND on the form that hosts the subform called frmQuotes.

I can sum the control [CostFT] in the subform footer but I want this value
poplulated in the TTLCostCND control

"Klatuu" wrote:

> What is it you are trying to Sum?
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "K" wrote:
>
> > Thanks for your time. Is there a way to complete this task?
> >
> > "Klatuu" wrote:
> >
> > > What you are calling a field is a control. It only contains one value, so it
> > > can't be summed. The DSum is a domain aggragate function, meaning it is used
> > > with tables or queries to sum a field.
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "K" wrote:
> > >
> > > >
> > > > Good day.
> > > >
> > > > I am trying to and populate a field in the main form "TTLCostCND" by
> > > > summing a field in a subform"CostFT. I get the error message Argument not
> > > > optional. Any help would be appreciated to achieve this goal
> > > >
> > > > Mainform is called "frmQuotes" - Table is called tblQoutes
> > > > Subform is called "frmQuotesSubform" - Table is called tblQuotesSubfrm
> > > >
> > > > Private Sub UOM_AfterUpdate()
> > > > Me.CostFT = Round(Me.CCRATE * Me.HRSCft / 100, 4)
> > > > Me.Form_frmQuotes!TTLCostCND = DSum(Me.CostFT)
> > > >
> > > > End Sub
> > > >
> > > > Thanks in advance.

==============================================================================

You received this message because you are subscribed to the Google Groups "microsoft.public.access.modulesdaovba"
group.

To post to this group, visit http://groups.google.com/group/microsoft.public.access.modulesdaovba?hl=en

To unsubscribe from this group, send email to microsoft.public.access.modulesdaovba-unsubscribe@googlegroups.com

To change the way you get mail from this group, visit:
http://groups.google.com/group/microsoft.public.access.modulesdaovba/subscribe?hl=en

To report abuse, send email explaining the problem to abuse@googlegroups.com

==============================================================================
Google Groups: http://groups.google.com?hl=en

Excel for Life

Rabu, 14 November 2007

25 new messages in 14 topics - digest

Tidak ada komentar:

Arsip Blog

Blogeroll

Mengenai Saya