Solved

MSACCESS VBA to copy Billing Address to Shipping Address - Bug

Posted on 2010-09-23
7
772 Views
Last Modified: 2012-05-10
I have some code that copies one address (Incident address) to another address field (Home address) at the push of a button in an Access 2007 form.  There is another button that copies the Home address to the Mailing address.

There are 2 buttons, then: btnCopyIncAdd, and btnCopyHomAdd.  Pressing a button should prompt "Are you sure?" with a yes or no.  If the user presses yes, the address is copied.

There are 2 problems:

Pressing btnCopyIncAdd causes Access to seemingly stop responding.  If I wait for about 10 seconds, then maximize a window (Chrome, Outlook, it doesn't really matter) on top of Access, then move that window away, the "Are you sure?" box shows up.  I can press yes, and it seems to work.

Pressing btnCopyHomAdd does the same thing, but it doesn't actually copy the home address to the mailing address.

See the VBA code below.  I have between beginner and intermediate knowledge of VBA and intermediate knowledge of Access.

This might have something to do with the Me. controls and how I have them set up.  I've tried them a couple different ways, but I can't remember if any worked.

Thanks!
Private Sub btnCopyHomAdd_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
If MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes Then
Else
MaiAddress = Me.HomAddress
MaiCity = Me.HomCity
MaiState = Me.HomState
MaiZip = Me.HomZip
Me.Requery
End If
End Sub

Private Sub btnCopyIncAdd_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
If MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes Then
Else
HomAddress = Me.IncAddress
HomCity = Me.IncCity
HomState = Me.IncState
HomZip = Me.IncZip
Me.Requery
End If
End Sub

Open in new window

0
Comment
Question by:Ubertam
  • 3
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33745097
use the Click event of the button

also, what are these?
MaiAddress
MaiCity
MaiState
MaiZip

are they textboxes in your form bound to fields of a table?
Private Sub btnCopyHomAdd_Click()
If MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes Then
Else
MaiAddress = Me.HomAddress
MaiCity = Me.HomCity
MaiState = Me.HomState
MaiZip = Me.HomZip
Me.Requery
End If
End Sub

Private Sub btnCopyIncAdd_Click()
If MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes Then
Else
HomAddress = Me.IncAddress
HomCity = Me.IncCity
HomState = Me.IncState
HomZip = Me.IncZip
Me.Requery
End If
End Sub

Open in new window

0
 
LVL 57
ID: 33745109
Some slight changes to the code:
Private Sub btnCopyHomAdd_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

If MsgBox("Are you sure?", vbQuestion + vbYesNo) = vbYes Then
Me.MaiAddress = Me.HomAddress
Me.MaiCity = Me.HomCity
Me.MaiState = Me.HomState
Me.MaiZip = Me.HomZip
Me.Requery
End If

End Sub

Private Sub btnCopyIncAdd_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)

If MsgBox("Are you sure?", vbQuestion + vbYesNo) = vbYes Then
Me.HomAddress = Me.IncAddress
Me.HomCity = Me.IncCity
Me.HomState = Me.IncState
Me.HomZip = Me.IncZip
 Me.Requery
End If

 End Sub  
I would also move this code to the OnClick event of the buttons, rather then the mouse up.
JimD.
0
 
LVL 5

Author Comment

by:Ubertam
ID: 33746846
@Capricorn1: these are fields in the table, not the textboxes.  The textboxes are named txt_MaiAddress, txt_MaiCity, etc.

@JDettman: When I set Me.MaiAddress = Me.HomAddress and so forth, it throws an error that Me.MaiAddress doesn't exist.

Using Capricorn1's question about that they are, it hinted to me to set the VBA to the text boxes rather than the database fields.  This worked (see code below) to copy the addresses perfectly.

Now the only problem that still exists (it is now set to click) is that "Are you sure?" doesn't fully pop-up.  It is no longer slow (I can maximize Chrome and drag it away and the button is there without me waiting 10 seconds).  It's still a pop-under-ish bug.
Private Sub btnCopyHomAdd_Click()
If MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes Then
Else
txt_MaiAddress = txt_HomAddress
txt_MaiCity = txt_HomCity
txt_MaiState = txt_HomState
txt_MaiZip = txt_HomZip
Me.Requery
End If
End Sub

Private Sub btnCopyIncAdd_Click()
If MsgBox("Are you sure?", vbQuestion + vbYesNo) <> vbYes Then
Else
txt_HomAddress = txt_IncAddress
txt_HomCity = txt_IncCity
txt_HomState = txt_IncState
txt_HomZip = txt_IncZip
Me.Requery
End If
End Sub

Open in new window

0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33746920
do a compact and repair
tools >database utilities > compact and repair..

then use this codes
Private Sub btnCopyHomAdd_Click()
If (MsgBox("Are you sure?", vbQuestion + vbYesNo)) = vbYes Then
txt_MaiAddress = txt_HomAddress
txt_MaiCity = txt_HomCity
txt_MaiState = txt_HomState
txt_MaiZip = txt_HomZip
Me.Requery
else
exit sub
End If
End Sub

Private Sub btnCopyIncAdd_Click()
If (MsgBox("Are you sure?", vbQuestion + vbYesNo)) = vbYes Then
txt_HomAddress = txt_IncAddress
txt_HomCity = txt_IncCity
txt_HomState = txt_IncState
txt_HomZip = txt_IncZip
Me.Requery
else
exit sub
End If
End Sub

Open in new window

0
 
LVL 5

Author Comment

by:Ubertam
ID: 33749551
That didn't do it.  I've attached the database.  See if it does it for you.  Thanks!
Flood-Cases-Shareable.accdb
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33749817
test this

i created a copy of the Contact Details form and deleted the textbox txtDuplicatesWarning - we'll discuss the function of this later
Flood-Cases-Shareable.accdb
0
 
LVL 5

Author Comment

by:Ubertam
ID: 33758690
That is definitely the problem.

It was referencing [Contacts Extended] instead of [Contacts Extreme].  Changing that solved it.  I suppose it was doing a whole lot of cross-querying and running 10 miles when it needed a yard (or metre to any Brits reading this).

Yay!  It works.  Thanks for the help!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question