Senin, 30 September 2013

[MS_AccessPros] Assistance needed with a report

 

I am trying to create a report from a query with the following fields:

mawb
clientno
hawb
desc
ctn
pkg
kgs

Having a problem with the layout of the report which should look like the following. For each MAWB there can be one or more HAWBs. Each HAWB has one Clientno, however each Clientno can have one or more Desc. Need to have each HAWB on a separate page.

Page 1

MAWB: 123-4567-8900
HAWB: 9876

Clientno: A123

Desc ctn pkg kgs
Printers 1 2 10

-------------------------------
Page 2

MAWB: 123-4567-8900
HAWB: 9888

Clientno: A123

Desc ctn pkg kgs
Printers 1 2 10
Ink 2 1 15
----------------------------------------

Thanks
Toukey

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

[MS_AccessPros] RE: Help with creating record change history

 

Thanks Stuart, that is exactly what I was looking for.  It will make things a lot easier.

Again thanks.

Rod



---In ms_access_professionals@yahoogroups.com, <saitchison@...> wrote:

Rod, Hi:

 

As far as I am aware (in that I have already used this facility) there is a property assigned to a field that gives you the old value.

 

You should be able to get it this way:  Me.conContactID.OldValue, where you would substitute your field name for the "conContactID" (i.e. the field name) bit.

 

Stuart Aitchison

 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dave@...
Sent: Mon, 30 September 2013 00:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Help with creating record change history

 

 


I usually capture the "current value" of the field durring the OnClick
event and assign it to a variable. Then durring the After Update event
use the variable and the new value in a docmd.runsql to append a new
record into an Activities table or Changes table kind of thing.

Quoting desertscroller@...:

I have a database with multiple users. When the users make changes to
the records; i.e., update fields on the records I would like to append
simple notes in a memo field which would indicate changes made
including user and date of change. Currently I only use a
message/response box to insure that user wants to overwrite existing
data. I think I can test the various editable fields as being dirty
but not sure how to retrieve the original value to be used in the note
being created.
My current approach is to use temp (original values) variables to
compare with new values. At typical note would be:
Due date changed from 9/20/13 to 10/15/13 by John on 9/12/13
Question are the pre-dirty values available before updating the
records?
Thanks for any help. (Using Access 2010 on Windows 7)
Rod

Links:
------
[1]
mailto:desertscroller@...?subject=Re%3A%20Help%20with%20creating%20record%20change%20history
[2]
mailto:MS_Access_Professionals@yahoogroups.com?subject=Re%3A%20Help%20with%20creating%20record%20change%20history
[3]
http://groups.yahoo.com/group/MS_Access_Professionals/post;_ylc=X3oDMTJlb2h0dnVlBF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTM4MDQ4MTE3Nw--
[4]
http://groups.yahoo.com/group/MS_Access_Professionals/message/105251;_ylc=X3oDMTM4c29uZmtmBF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBG1zZ0lkAzEwNTI1MQRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzEzODA0ODExNzcEdHBjSWQDMTA1MjUx
[5]
http://groups.yahoo.com/group/MS_Access_Professionals/members;_ylc=X3oDMTJmdDJ2NTg2BF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzODA0ODExNzc-?o=6
[6]
http://groups.yahoo.com/group/MS_Access_Professionals;_ylc=X3oDMTJldGlzYjQxBF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTM4MDQ4MTE3Nw--
[7]
http://groups.yahoo.com/;_ylc=X3oDMTJkbGloNTcwBF9TAzk3NDc2NTkwBGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzgwNDgxMTc3
[8]
mailto:MS_Access_Professionals-traditional@yahoogroups.com?subject=Change
Delivery Format: Traditional
[9]
mailto:MS_Access_Professionals-digest@yahoogroups.com?subject=Email
Delivery: Digest
[10]
mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com?subject=Unsubscribe
[11] http://info.yahoo.com/legal/us/yahoo/utos/terms/
[12] mailto:ygroupsnotifications@yahoogroups.com?subject=Feedback on
the redesigned individual mail v1

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Recent Activity:
.

__,_._,___

[MS_AccessPros] RE: Access Security...

 

Myra 


Don't tell anyone, but there are 3rd-party softwares out there that can crack the passwords and user names. But I've never found a free one. The one I use is Passware's Passware Kit Standard. It's at lostpassword.com. I've been using it for years and it's never failed me. You do have the MDW file, right?


Don't download it from anywhere else (especially the ones that claim it's free). You could end up with some nasty viruses.



---In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

I am trying to remove Access security from an MDB so that I can migrate it to 2010.  This was created years ago by who knows who and you can probably guess what I don't have.  I don't have the admin password.  Or course I don't have the PID.  I know it's breakable, but I don't know how…  I would NEVER use access security because I know the likelihood of retaining the Admin ID and the PID is Pretty slim. 

 

I've searched our site I am nervous about going to the web without smart person blessing.  Thanks. 

 

Myra

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
.

__,_._,___

[MS_AccessPros] Access Security...

 

I am trying to remove Access security from an MDB so that I can migrate it to 2010.  This was created years ago by who knows who and you can probably guess what I don’t have.  I don’t have the admin password.  Or course I don’t have the PID.  I know it’s breakable, but I don’t know how…  I would NEVER use access security because I know the likelihood of retaining the Admin ID and the PID is Pretty slim. 

 

I’ve searched our site I am nervous about going to the web without smart person blessing.  Thanks. 

 

Myra

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

RE: Re: [MS_AccessPros] DoCmd.TransferText

 

Yea! Just so you know...I always use import/export specs. They give you much more control over just TransferText without one.


Bill



---In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

Perfect Bill, thank you!
Patty

Sent from my mobile device

On Sep 28, 2013, at 12:29 PM, "Bill Mosca" <wrmosca@comcast.net> wrote:

 

Patty

 

Start a manual import. When you get to the wizard there is a checkbox to skip the first row. When you get it set up the way you want click on the Advanced button and save the setup as a Spec. Then you reference the spec in the DoCmd.TransferText command as one of the arguments.

 

Regards,

Bill

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Friday, September 27, 2013 2:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] DoCmd.TransferText

 

 

I am trying to import a text file, using the DoCmd.TransferText acImportDelim method.
 
The text files that I need to import have first row field names which are delimited by " ".
 
The data is tab delimited.
 
What is occurring is  that the first row is setting the pattern for how the data is delimited. After the first row is imported, each subsequent row is imported entirely into the first field/column of each subsequent row.
 
So, the first row is  correctly delimited and populating the table, but all the following rows are not.
 
I don't need the first row, but I don't know how to start the TransferText at row 2, if that is even possible.
 
If someone has a suggestion for me, I would be grateful.
 
Thank you,
 

--
Patricia Mapes

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)
Recent Activity:
.

__,_._,___

Re: [MS_AccessPros] DoCmd.TransferText

 

Perfect Bill, thank you!
Patty

Sent from my mobile device

On Sep 28, 2013, at 12:29 PM, "Bill Mosca" <wrmosca@comcast.net> wrote:

 

Patty

 

Start a manual import. When you get to the wizard there is a checkbox to skip the first row. When you get it set up the way you want click on the Advanced button and save the setup as a Spec. Then you reference the spec in the DoCmd.TransferText command as one of the arguments.

 

Regards,

Bill

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Friday, September 27, 2013 2:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] DoCmd.TransferText

 

 

I am trying to import a text file, using the DoCmd.TransferText acImportDelim method.
 
The text files that I need to import have first row field names which are delimited by " ".
 
The data is tab delimited.
 
What is occurring is  that the first row is setting the pattern for how the data is delimited. After the first row is imported, each subsequent row is imported entirely into the first field/column of each subsequent row.
 
So, the first row is  correctly delimited and populating the table, but all the following rows are not.
 
I don't need the first row, but I don't know how to start the TransferText at row 2, if that is even possible.
 
If someone has a suggestion for me, I would be grateful.
 
Thank you,
 

--
Patricia Mapes

 

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (5)
Recent Activity:
.

__,_._,___

RE: Re: [MS_AccessPros] Separator / comma for row count field

 

You're welcome, Patty.


Bill  



---In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

Thank you Bill. I'm going to try that.
Patty

Sent from my mobile device

On Sep 28, 2013, at 12:22 PM, "Bill Mosca" <wrmosca@comcast.net> wrote:

 

Patty

 

In the past, I have had issues with number formatting especially when using SQL Server linked tables. If you are still having trouble try forcing the formatting like this:

 

Control Source: =Format(Count([LastName]),"0,000")

 

Regards,

Bill

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Thursday, September 26, 2013 5:36 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Separator / comma for row count field

 

 

Hmm... I don't know is that is good or bad news.
I should say, the totals at the end of the report also respond that way.
Mysterious.
Maybe I have a 2007 issue. I will look into that more,
You are helpful though; to know that is often the solution makes it less despairing!
Thank you,
Patty

Sent from my mobile device

On Sep 26, 2013, at 7:22 PM, Duane Hookom <duanehookom@hotmail.com> wrote:

> I just did a test in Access 2010 with a text box in the Group Footer with these properties:
>
> Control Source: =Count([LastName])
> Format: Standard
> Decimal Places: 0
>
> My numbers appear exactly how expected.
>
> Duane Hookom MVP
> MS Access
>
>
> ----------------------------------------
>> From: patty.mapes@gmail.com
>>
>> I got this report from a client. Many of the counts are in the tens of thousands. I wanted to format the numbers to include the comma separators for thousands to make the numbers more readable.
>>
>>
>>
>> On Sep 26, 2013, at 6:32 PM, Duane Hookom <duanehookom@hotmail.com> wrote:
>>
>>> Patty,
>>>
>>> So your control source is something like:
>>> =Count([SomeFieldName])
>>>
>>> What do you mean by "does not work".
>>>
>>> Duane Hookom MVP
>>> MS Access
>>>
>>> ----------------------------------------
>>>> From: patty.mapes@gmail.com
>>>>
>>>> It is the count of records in a grouping? On the report; make sense?
>>>>
>>>> Sent from my mobile device
>>>>
>>>> On Sep 26, 2013, at 6:17 PM, Duane Hookom <duanehookom@hotmail.com> wrote:
>>>>
>>>>> Patty,
>>>>>
>>>>> What is the control source property of your "row count field"? Are you using a running sum property of the control or is this value from the record source?
>>>>>
>>>>> Duane Hookom MVP
>>>>> MS Access
>>>>>
>>>>> ----------------------------------------
>>>>>> From: patty.mapes@gmail.com
>>>>>>
>>>>>>
>>>>>> I have a report with a row count field in it. I would like to use a standard format, with no decimal point and a comma/separator. I have zero in the decimal places in the property sheet and that works fine. Using "Standard" in the format box in the property sheet does not work.
>>>>>>
>>>>>> I appreciate any suggestions.
>>>>>>
>>>>>> The database I'm working on is 2007, and I have 2010 on this machine.
>>>>>>
>>>>>> Patty
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (10)
Recent Activity:
.

__,_._,___

RE: Re: [MS_AccessPros] DoCmd.TransferText

 

You're welcome, Patty.


Bill 



---In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

Thank you Bill.
I will do that.


Patty
On Sep 28, 2013, at 1:29 PM, Bill Mosca wrote:

 

Patty

 

Start a manual import. When you get to the wizard there is a checkbox to skip the first row. When you get it set up the way you want click on the Advanced button and save the setup as a Spec. Then you reference the spec in the DoCmd.TransferText command as one of the arguments.

 

Regards,

Bill

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Friday, September 27, 2013 2:39 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] DoCmd.TransferText

 

 

I am trying to import a text file, using the DoCmd.TransferText acImportDelim method.
 
The text files that I need to import have first row field names which are delimited by " ".
 
The data is tab delimited.
 
What is occurring is  that the first row is setting the pattern for how the data is delimited. After the first row is imported, each subsequent row is imported entirely into the first field/column of each subsequent row.
 
So, the first row is  correctly delimited and populating the table, but all the following rows are not.
 
I don't need the first row, but I don't know how to start the TransferText at row 2, if that is even possible.
 
If someone has a suggestion for me, I would be grateful.
 
Thank you,
 

--
Patricia Mapes

 



__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Recent Activity:
.

__,_._,___

[MS_AccessPros] RE: Enable and Disable the First and Last navigation cmdbuttons

 

Phucon


This is how I do it:


'Form's current event:


Private Sub Form_Current()

    Call CheckPosition(Me)

    Me.lblRecCt.Caption = Me.CurrentRecord & " of " & Me.Recordset.RecordCount


End Sub



'Public routine called by all forms as needed. Occasionally, I need to pass

'an array of controls to re0query, but not very often.

Public Sub CheckPosition(frm As Form, ParamArray ctrls())

'Purpose  : Enable/disable nav buttons.

'           txtFocus is a textbox that is used only to get

'           Focus during enabling/disabling. It is just a

'           dot on the form placed near nav buttons.

'DateTime : 11/29/2006 07:50

'Author   : Bill Mosca

    Dim lngRecNum As Long

    Dim lngRecCt As Long

    Dim x As Integer

    

    

    lngRecNum = frm.CurrentRecord

    '    frm.RecordsetClone.MoveFirst

    If lngRecNum < frm.Recordset.RecordCount Then

        frm.RecordsetClone.MoveLast

    End If

    lngRecCt = frm.RecordsetClone.RecordCount

    frm.txtFocus.SetFocus

    frm.cmdGoFirst.Enabled = lngRecNum <> 1

    frm.cmdGoPrev.Enabled = lngRecNum <> 1

    frm.cmdGoNext.Enabled = lngRecNum < lngRecCt

    frm.cmdGoLast.Enabled = lngRecNum < lngRecCt

    frm.cmdGoNew.Enabled = frm.Recordset.RecordCount <> 0

    

    'No Additions should disable "New" button

    If frm.AllowAdditions = False Then

        frm.cmdGoNew.Enabled = False

    Else

        frm.cmdGoNew.Enabled = frm.Recordset.RecordCount <> 0

    End If


    'If any cbo's or lst's have a user-defined function for RowSourceType

    'they will need to be requeried.

    If Not IsNull(ctrls) Then

        For x = 0 To UBound(ctrls)

            If ctrls(x).ControlSource = "" Then ctrls(x) = Null

            ctrls(x).Requery

        Next

    End If


End Sub


Regards,

Bill Mosca, Founder - MS_Access_Professionals

http://www.thatlldoit.com

Microsoft Office Access MVP

https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E

My nothing-to-do-with-Access blog

http://wrmosca.wordpress.com


 



---In MS_Access_Professionals@yahoogroups.com, <ms_access_professionals@yahoogroups.com> wrote:

I use these 2 statements to Enable and Disable the First and Last navigation cmdbuttons. The syntext looks weir to me, but it works ok so far. Is it an appropriate way?  What are other alternatives?

With Me

    !lblTotRec.Caption = "Record " & rs.AbsolutePosition + 1 & " of  " & rs.RecordCount

    !cmdLast.Enabled = Not Me.CurrentRecord = rs.RecordCount               

    !cmdFirst.Enabled = Not rs.AbsolutePosition + 1 = 1

End With

Thanks

Phucon

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
.

__,_._,___

[MS_AccessPros] Enable and Disable the First and Last navigation cmdbuttons

 

I use these 2 statements to Enable and Disable the First and Last navigation cmdbuttons. The syntext looks weir to me, but it works ok so far. Is it an appropriate way?  What are other alternatives?

With Me

    !lblTotRec.Caption = "Record " & rs.AbsolutePosition + 1 & " of  " & rs.RecordCount

    !cmdLast.Enabled = Not Me.CurrentRecord = rs.RecordCount               

    !cmdFirst.Enabled = Not rs.AbsolutePosition + 1 = 1

End With

Thanks

Phucon

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
.

__,_._,___

RE: [MS_AccessPros] Help with creating record change history

 

Rod, Hi:

 

As far as I am aware (in that I have already used this facility) there is a property assigned to a field that gives you the old value.

 

You should be able to get it this way:  Me.conContactID.OldValue, where you would substitute your field name for the "conContactID" (i.e. the field name) bit.

 

Stuart Aitchison

 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dave@panthertrax.com
Sent: Mon, 30 September 2013 00:14 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Help with creating record change history

 

 


I usually capture the "current value" of the field durring the OnClick
event and assign it to a variable. Then durring the After Update event
use the variable and the new value in a docmd.runsql to append a new
record into an Activities table or Changes table kind of thing.

Quoting desertscroller@cox.net:

I have a database with multiple users. When the users make changes to
the records; i.e., update fields on the records I would like to append
simple notes in a memo field which would indicate changes made
including user and date of change. Currently I only use a
message/response box to insure that user wants to overwrite existing
data. I think I can test the various editable fields as being dirty
but not sure how to retrieve the original value to be used in the note
being created.
My current approach is to use temp (original values) variables to
compare with new values. At typical note would be:
Due date changed from 9/20/13 to 10/15/13 by John on 9/12/13
Question are the pre-dirty values available before updating the
records?
Thanks for any help. (Using Access 2010 on Windows 7)
Rod

Links:
------
[1]
mailto:desertscroller@cox.net?subject=Re%3A%20Help%20with%20creating%20record%20change%20history
[2]
mailto:MS_Access_Professionals@yahoogroups.com?subject=Re%3A%20Help%20with%20creating%20record%20change%20history
[3]
http://groups.yahoo.com/group/MS_Access_Professionals/post;_ylc=X3oDMTJlb2h0dnVlBF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTM4MDQ4MTE3Nw--
[4]
http://groups.yahoo.com/group/MS_Access_Professionals/message/105251;_ylc=X3oDMTM4c29uZmtmBF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBG1zZ0lkAzEwNTI1MQRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzEzODA0ODExNzcEdHBjSWQDMTA1MjUx
[5]
http://groups.yahoo.com/group/MS_Access_Professionals/members;_ylc=X3oDMTJmdDJ2NTg2BF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzEzODA0ODExNzc-?o=6
[6]
http://groups.yahoo.com/group/MS_Access_Professionals;_ylc=X3oDMTJldGlzYjQxBF9TAzk3MzU5NzE0BGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTM4MDQ4MTE3Nw--
[7]
http://groups.yahoo.com/;_ylc=X3oDMTJkbGloNTcwBF9TAzk3NDc2NTkwBGdycElkAzY0NDg5NzgEZ3Jwc3BJZAMxNzA1MTE1MzcwBHNlYwNmdHIEc2xrA2dmcARzdGltZQMxMzgwNDgxMTc3
[8]
mailto:MS_Access_Professionals-traditional@yahoogroups.com?subject=Change
Delivery Format: Traditional
[9]
mailto:MS_Access_Professionals-digest@yahoogroups.com?subject=Email
Delivery: Digest
[10]
mailto:MS_Access_Professionals-unsubscribe@yahoogroups.com?subject=Unsubscribe
[11] http://info.yahoo.com/legal/us/yahoo/utos/terms/
[12] mailto:ygroupsnotifications@yahoogroups.com?subject=Feedback on
the redesigned individual mail v1

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
.

__,_._,___