VBA Code - Checkbox - Label problem

I'm in a bit of a mess...

Okay, I have some VBA code that causes a label on my main form to change depending on the value that is in the check box in the record.

The idea:
The idea of part of my form is for the user to have to check a tick box to know that the 'thing' can be released. It's to make the user more aware. All 'things' are asumed to be in the category of 'Can't be released' until notified and the user checks the box in the form and thus the VBA should change the content of the label.


Existing Code:

	Code:
	Private Sub tickrelease_AfterUpdate()

    If Me.tickrelease = True Then
    Me.lblrelease.ForeColor = vbGreen
    Me.lblrelease.Caption = "Can be released"
Else
    Me.lblrelease.ForeColor = vbRed
    Me.lblrelease.Caption = "Can't be released"
End If

End Sub

The default value of the check box when a new record is created is no tick in the box, and default caption of the 'lblrelease' is "Can't be released", with the same red that is indicted in the code above.


The Problem:
The code to cause the label to change color/text does work to a degree. For example - if the check box is ticked then the text and color in the label will change to, and if there's no check in the check box then different color and text appears.

However, what I have noticed is that the label change doesn't stay bound to that record when browsing through the records on the form or creating a new record the colours from the previous record stays with the label into the next new record or new browsed record (the checkboxes themselves stay bound to the form/record and work fine - no problem with that) it just seems the content of the labels isn't working properly as I intented and would like the changes in the label caused by the text box to stay bound to that record.

I hope you guys understand. If not I can upload the database that can show you guys the problem.

Thanks for any help you may give.


Post your answer or comment

comments powered by Disqus
Hi,
when i surf on the internet, i sometimes find vba coding.
The only problem is that when i copy it in the vba application and run it,
it tells me to look up for a macro before running it
What is the problem?

Thanks

Hi all,
I am working on a database system and I'm using checkboxes.



In the Water treatment part, when I check any of these checkboxes, I get the value true to all records. While it should be True when checked and False when not.
I'm using this vba code to control it:

Private Sub Form_Current()
a = Water_Treatment
If a - 8 >= 0 Then
a = a - 8
Aqua_tablet = True
Else
Aqua_tablet = False
End If
If a - 4 >= 0 Then
a = a - 4
Boiling = True
Else
Boiling = False
End If
If a - 2 >= 0 Then
a = a - 2
Clorination = True
Else
Clorination = False
End If
If a - 1 >= 0 Then
a = a - 1
wFilter = True
Else
wFilter = False
End If
End Sub

Private Sub Boiling_AfterUpdate()
If Boiling = True Then
Water_Treatment.Value = Water_Treatment.Value + 4
Else
Water_Treatment.Value = Water_Treatment.Value - 4

End If

End Sub

Private Sub Clorination_AfterUpdate()
If Clorination = True Then
Water_Treatment.Value = Water_Treatment.Value + 2
Else
Water_Treatment.Value = Water_Treatment.Value - 2

End If

End Sub


Private Sub Aqua_tablet_AfterUpdate()
If Aqua_tablet = True Then
Water_Treatment.Value = Water_Treatment.Value + 8
Else
Water_Treatment.Value = Water_Treatment.Value - 8

End If

End Sub


Private Sub wFilter_AfterUpdate()
If wFilter = True Then
Water_Treatment.Value = Water_Treatment.Value + 1
Else
Water_Treatment.Value = Water_Treatment.Value - 1

End If

End Sub



Can anybody help me with this ?
Thank you in advance

Regards,

Hi Guys

I have a combobox with 10 departments. On the form there are 10 labels as well.My requirement is when I select a dept name from the combobox then it should display that on label1 and for the rest of the labels, all other departments should be displayed.
Now suppose I select another department from the combobox then again on label1 the current selected department name should be displayed and the rest of the labels would display the other 9 departments.

Can anyone please help me with the vba code for this please.I have written the following code in excel/vba but combobox list property is not working in access/vba.

	Code:
	Private Sub Combo8_LostFocus()
Dim lngIndex As Long
    Dim lngControlIndex As Long
    
    lngControlIndex = 18
    For lngIndex = 0 To Combo8.ListCount - 1
        If lngIndex = Combo8.ListIndex Then
            Me.Controls("Label18").Caption = Combo8.List(lngIndex)
        Else
            lngControlIndex = lngControlIndex + 1
            Me.Controls("Label" & lngControlIndex).Caption = Combo8.List(lngIndex)
        End If
    Next
End Sub

Regards
AMan

This may seem a simple problem but if anyone can help with some VBA code it would be a great help.

I have a label on a form in an access database. The form is a data entry form for the main table in the database. One of the fields is a yes/no and appears on the form as a standard check box.

The label in question has its visible property set to false so you cannot normally see it. I only want the label to be visible if when scrolling through the records that I mentioned above, the check box is checked (ie its value is -1) for that particular record.

I can't seem to find a suitable event make this work.

I would appreciate some assistance.

Hello

I am developing an Access 2000 application in which forms are altered dynamically based on options chosen by the user.

The CreateObject Function places checkboxes and other objects on a form, and this works fine.

The key line of code is:

Set ctlCheck = CreateControl(frm.Name, acCheckBox, , , "", posX, posY, chkWidth, chkHeight)

My problem is that I want to place the objects on pages of a tab control. I can do this in 'click and point' mode, but when I try it with VBA code, the objects are attached to the form, not the tab control page.

Any suggestions on how to tie the CreateControl command to a tab control page?

Things I have tried without success include:

a) using the page object name as the form name (the first parameter in the brackets) - it appears that the object must be a form.

b) using the page objects collection as the parent.

Thanks for your help

Dan

It's a report that is called from a Main Screeen. The VBA is located in the detail Onformat section of the report. This is what I got the code down too. I used to have it in the OnPage for the report, however, after some reading I found that it doens't update the OnPage until after the formating. So it was suggested that I use the OnFormat of the Detail section. This still doesn't really work. I have a field on the report that has the checkbox for rushed and the PO_Number is listed on the report as well. I want to have the TextBox93 (I know I didn't name it, Yet!) say Rush Order if in the table the field Rushed is checked. This is filled via a different form when they enter their purchase orders. I took out the responce code to see how it flowed without the stops. One thing I did notice is when it was using the msgbox's it seems to evail this code twice before displaying the first page.
Rushed = the field that is a checkbox that is stored as a lookup and yes/no
so I am seeing in my testing that is either 0 or -1
PO List = my table that stores all the information about each PO.
Me![PO_Number] is the po_number the query pulled to fill the report and is on the report and
[PO_Number] = should be what is pulled from the table PO List to be compared with.

Let me know if you need anything else.... thanks.

VBA:

	Code:
	Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
    Dim test 
    test = DLookup("[Rushed]", "[PO List]", [PO_Number] = Me![PO_Number]) 
    If test = "-1" Then 
        Me.Controls!Text93 = "Rush Order" 
    Else 
        Me.Controls!Text93 = "" 
    End If 
End Sub



I need to create an access report uisng VBA code. i want to be able to create a report name and save it and also create labels and text boxes in the report using the vba code and those fields should come from the table in the database. and data of the table should be displayed in the report under each field in the report as well.

im new to access and vba. and i want to get this job done quickly.someone please please help me on this and it would be really greatfull. i hope there'll be someone to help me........

Hello,

I'm using Access 2010 and found the Send to Email as PDF command indispensible. However, I have a problem in that I've hidden the main ribbon from my users which means they can't actually click that button.

Can anyone tell me the VBA code that will let me PDF and email an indiviual report based on a given unique ID. I am happy for the user to add the email address, subject and message body and for them to click send.

Thanks

Hello, it's the first time I write here.
I have a problem with Access 2007.
I am not a VBA-expert, so you can better understand my difficulties.
I am trying to create, in access, a report based on excel documents. Once it will be finished, the report should display specific cells of any excel documents imported into access.
My actual problem concerns a filtered table I have created in access: I should produce the command (very simple in Excel) to delete a cell in a column and shift up all the cells in the same column. after, I should create text boxes, taking only certain cells from the table, and displaying their contents into the report.
Now, how can I do? Do you know which is the VBA code to delete and shift up in Access?

Thanks

I have been using 2003 for a while but I have switched to 2010 recently.

I have just found out that if you use the wizards when creating a command button, Access now creates a macro instead of creating VBA code (for opening a form, for example)

I prefer code over macros, so is there a way to change this?

Also, what about the macros that have already been created?? How can those be converted to VBA?..When I use the "Convert From's Macors to Visual Basic" option I get a "There was a problem opening the macro "[Embeded Macro]". Do you want to continue?" and the macro does not get converted.

mafhobb

Here the scenario:

I want to analyze forms via VBA code and see which controls have events behind them and of what type they are (Event Procedure, Macro or Expression).

Now the problem:

I have code for the standard controls like Text Boxes, Option Buttons etc. that works fine.


	Code:
	EventDefinition = Control.Properties("")

or a bit more real life:

	Code:
	sEventDefinition = MyForm!txtTextBox.Properties("OnDblClick")

This throws (a catchable) run time error if the event does not exist for the control in question. If it exists, sEventDefinition will contain an empty string, the Expression, the Macro name or the term "[Event Procedure]". This way I can find out in VBA code if a theoretically possible event is in use for that control and what it does.

The approach above does only partially work for ActiveX controls like Tree Views. Some Events (like OnClick, OnKeyPress etc.) cannot be read that way. They are also not detectable by enumerating the control properties (only five events commmon to all ActiveX controls are listed, but not all).

So I am looking for VBA code that can read ALL event definitions of ActiveX controls.

Anyone, any thoughts?
Thomas

Quote: The RecordSource on this form is set in VBA code and not in the Properties window.
i was trying to implement a code i found on the web with an example

database showing what i am trying to achieve .

it's an easy example showing how to retrieve data in a subform based on

a combo box in the main form ; ( form / subform ) .


when ever I rename the subform , change all the related info (in

properties / vb code )

a problem shows up in the line shown in red .



	Code:
	Sub SetFilter()
    Dim LSQL  As String
    
    LSQL = "select * from Customers"
    LSQL = LSQL & " where CustomerID = '" & cboSelected & "'"
    
    Form_frmCustomers_sub.RecordSource = LSQL    
    
End Sub


for some reason the "RecordSource" is not showing anymore .

when i went back to the example .. i found the following note :

Quote: The RecordSource on this form is set in VBA code and not in the Properties window.
which i think is what causing the problem ...

but frankly , i didn't know what does it mean when " the record source is set in vba NOT properties window " ..




any idea where i messed up !?

help is appreciated ..

access 2007

Hi,

I have below tables in my db.

1. T_ProductMaster (Fields are ComboCode, ProdCode, ProductName, Stock etc)
2. T_ComboProd (Fields are ComboCode, ProdCode, ProductName & Qty)
3. T_PurchaseFooter (Fields are ProdCode, ProductName, PurQty etc.)

What I am trying to do is as below:

Stock in T_ProductMaster tbl is updating perfectly when I am purchasing single-single material qty feeding thru T_PurchaseFooter (tbl/form) while making purchase entry.

The main problem is when I purchase the combo box which contents around 25 items and are already in T_ProductMaster table, the entry in T_PurchaseFooter is only single because Combo Box has product code 1. So user will enter Purqty as 1.

What I need is once the combo box is entered (prodcode 1) and cofirm thru a check mark, all the 25 items should be update stock in T_ProductMaster.

To do this I have created separate table which is table # 2. T_ComboProd tbl for all the items coming in combo box along with their qty.

Let us say I have 3 items in my T_ProductMaster

ProdCode-----ProductName-------Stock
14-----------Aloe Bath Gelee----- 3
15-----------Aloe Vera Gel--------0
39-----------Arctic Sea-----------5

Let us say tbl T_ComboProd has 2 items for Combo Box.

ProdCode----ProductName--------Qty------ComboCode
15----------Aloe Vera Gel-------- 8 ---------- 1
39----------Arctic Sea----------17----------- 1

When I enter single for each product in T_PurchaseFooter, it is updating stock perfectly, But If I make entry of Combo box (single entry) it should check all the items of Combo box from tbl T_ComboProd and get their respective qty from same tbl and update to T_ProductMaster tbl. This is being done after confirmation thur a check mark in the same record set line.

So the stock in T_ProductMaster tbl (for those combo box items from T_ComboProd) should like below

ProdCode-----ProductName-------Stock
15-----------Aloe Vera Gel--------8
39-----------Arctic Sea-----------22

I have written below vba code to do this and it is updating all the respective items from T_ComboProd to T_ProductMaster tbl BUT it produces below error once the item is finished from the T_ComboProd.

Run-time error ‘3021’
No current record


If Confirm.Value = -1 Then

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim TempCode
Dim TempQty, NEWPURQTY
Dim X: X = 0
TempCode = ProdCode

If Me.ProdCode = 1 Then

Set rst1 = CurrentDb.OpenRecordset("Select * from T_ComboProd Where ComboCode = " & ProdCode)

rst1.MoveLast
rst1.MoveFirst

Do While Not rst1.RecordCount


For X = 1 To rst1.RecordCount
Set rst2 = CurrentDb.OpenRecordset("Select * from T_ProductMaster where XCode = " & rst1!ComboCode & _
" And ProdCode = " & rst1!ProdCode & "")

NEWPURQTY = rst1!Qty
TempQty = rst2!Stock
rst2.Edit
rst2!Stock = TempQty + NEWPURQTY
TempQty = 0
NEWPURQTY = 0
rst2.Update
rst2.Close

X = X - 1
If X >= rst1.RecordCount Then
Exit Do
Else

rst1.MoveNext
End If
Next X
Loop

End If
rst1.Close


Else

HERE IT IS PERFOMING OTHER INSTRUCTION IF ProdCode is not 1.
.....
.......
.........

I believe the loop is not in proper way.

One more thing, I will have many combo boxes in future and I will enter all their respective items in T_ComboProd tbl with their ComboCode. So how can I handle if there are entries other than combo box 1?

Can somebody help?

If need I can upload my db.

Thanks in advance.

With kind regards,
Ashfaque

Hi ,

Could someone please help me with the VBA code behind my post button which is on my mainform. What I want is to check that the following controls have been populated. The first three work fine (Title, TransactionDate and Username) but as soon as I try the subform it doesn't work properly. If I deliberately leave out say CC and then click the post button it say's "please enter a/c no" and then comes up with "There is an invalid method in an expression".

I'm probably doing something really daft.

cheers
Wrightie


This is the code I'm using ...


	Code:
	 
Private Sub Post_Click()
On Error GoTo Err_Post_Click
If Len(Nz(Title, "")) = 0 Then
   MsgBox "You must enter a Title", , "Input Error!"
   Cancel = True
   Title.SetFocus
   Exit Sub
End If
If Len(Nz(TransactionDate, "")) = 0 Then
   MsgBox "You must enter a Transaction Date", , "Input Error!"
   Cancel = True
   TransactionDate.SetFocus
   Exit Sub
End If
If Len(Nz(UserName, "")) = 0 Then
   MsgBox "You must select a Username", , "Input Error!"
   Cancel = True
   UserName.SetFocus
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.AcNo, "")) = 0 Then
   MsgBox "Please enter an A/c No", , "Input Error!"
   Cancel = True
   Me.Journal_Entries_Subform.Form.SetFocus
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.CC, "")) = 0 Then
   MsgBox "Please select a CC", , "Input Error!"
   Cancel = True
   Me.Journal_Entries_Subform.Form.SetFocus
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.Dept, "")) = 0 Then
   MsgBox "Please enter a Department", , "Input Error!"
   Cancel = True
   Me.Journal_Entries_Subform.Form.SetFocus
   Exit Sub
End If
End Sub



Hi,

I have a few doubts regarding how to make SQL queries to link tables from MS access buttons using VBA coding.

Given: I have a list of link tables connecting Oracle and MS access with info stored in these tables.I also have a list of text boxes and drop down lists also to get information from the user . Now when the user clicks on search button.

Requirements:
I have to search for the the particular row in the table given these input values for the primary key ( or composite key ) and then after retrieving the row from the table I have to print these values on to a set of text boxes in the form .

Please guide me as to how to go about this problem.

I would be thankful if you could also give me a sample code to understand

Thanks in advance for the help ,

Hi,

I have till now used MS access tables for database and thus peacefully used Dlookup for selecting a particular column value and insert and delete and update for row modifications.

Now I am going to destroy these tables and link my MS access forms to oracle database. I would like to make the specified modifications to the VBA code of the buttons .

1. Is dlookup also applicable to Oracle or should i write a select SQL command. If so how to retrieve just a column value using SELECT command and assign it to a text box. ( Problem here is that the SQL when run returns a record set not just a single variant.

2. What modifications should i do before i execute a SQL command for insert delete and update. could you please give the syntax for connecting to oracle database.

thanks in advance for the help provided.

This might be something for the Forms discussion board. But it also involves VBA coding.

I have 3 bound text boxes which I have placed in the footer section of a form. The footer section is invisible to the form user.

The user enters data into 3 unbound text boxes which are visible and then clicks on a SAVE command button to save the record.

The VBA code for the command button transfers the entries from the unbound text boxes into the invisible text boxes and saves the record.

My problem is that the VBA code keeps changing the name of one of the bound text boxes after I enter the code.

I usually put the prefix "txt" in front of Text Box names. In this case, I used the following names:

txtepisodeID
txtfunctionID
txtnameID

When I key in the VBA coding and hit enter, ACCESS capitalizes the first "E" in the text name as follows: txtEpisodeID

So I decided to rename all the Text Box names to:

txtEpisodeID
txtFunctionID
txtNameID

for the sake of consistency.

Oddy enough, the VBA coding does not allow the "F" and the "N" to be capitalized.

The coding seems to work fine, but this capitalization in some cases, but not other cases is very odd.

Does anyone have any idea why this is happening?

Hello.


I am trying to create a VBA code that will extract certain information from a pivot table and put this into a table on another sheet.

for exmple: I am trying to get all the Male (M) entries in the pivot table into a table titled 'Male Table' How do I go about doing this...?

I also have a problem as when the pivot table gets updated with more data, the number of Males entries will go up etc, and therefore data will be in different cells compared to the previous week. So I need the coding to pick up all male entries within the pivot table, regardless of their cell reference, and copy the data across into the 'male Table'

Please advise as I have been struggling with this for some time now.
Thanks for your help in advance

Regards

Carl

Hello all,

Some of you may have seen this post in the accessforums.net queries thread, but figured I would expand to see what you folks could come up with.

I have run into a wall in writing the final piece of my DB. This DB is a photo archive that is linked to mapping software. The issue that I have is in creating the final table that the mapping software needs to read in order to display a correct layer that includes file path links to the associated images.

The final table does not really follow standard Access DB rules for a table since it forces multiple sets of images into the same record row. Here is the basics for my DB:

Table: Photo_File (stores the Unique_ID and file base for each entry)
Table: Project (Stores the Unique_ID, project name, and project area)
Table: Spatial (Stores the Unique_ID, UTM coordinates (2), LAT/LON, R2Veg_Loc (specific to the map layer), and Stand_Num (forest stand))

Queries: Photo_Link Query (creates the table Photo_Link and builds the file paths to the images)

I have 2 other queries that I am trying to rid myself of since they only lead to problems with duplication. Hence VBA coding. BUT, I am not a VBA officionado, so I am asking for some serious help.

I need to write VBA code that accomplishes this:
Searches the Photo_Link table for Easting_UTM AND Northing_UTM coordinates. Writes the values into a new table. Writes the values of the location, stand, photo year and image paths into the same row. THEN, matches other records with the same coordinate set and writes the second (third, fourth, etc) photo year and image paths into the same row. Loops through until end of records. I can't use a concatenate function to do this since all the image paths for a coordinate set and years need to be in separate fields, but the same row.

The final table should look something like this:

[Easting_UTM][Northing_UTM][R2veg_Loc][Stand_Num][Photo_Year][IMG_North][IMG_East][IMG_South][IMG_West][Photo_Year2][IMG_North2][IMG_East2][IMG_South2][IMG_West2][.....

Let me know if I need to post the sample DB.

I need to get this done ASAP.

Thanks,

Adam

Hi there.
A few days ago my database suddenly started pumping out this error message:

"The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code form modules, forms and reports. You should back up your database before attempting to open the database and delete the VBA project."

This message appears every time a form with VBA code is opened, and every time I try to open the VBA editor. Of course I have a backup, however I rather not be forced to redo about 6 hours of work.

Looking across the internet it seams like quite a few people have had this problem, but no solutions. As far is I can tell, something in the VBA code has caused the database to become corrupted.

So my question is this, is there any way I could possibly recover at least some of the code? Could I somehow force Access to show me the code, even if a part of it is corrupt?

Hello,

Thank you for your interest in my problem. I'm currently having an issue with my VBA code (posted below)...or should I say, I don't know what I'm doing and I don't know how to fix my problem
So I've got a check box, that when selected "true", enables a text box and visa-versa. The function seems to work good, but when I've got two problems. One is that when the form opens, even though the check box is selected, it has the text box disabled (with data entered in the box).
The other problem is that I don't know how to have two instances of the same code. For example, I've got my "Parts_On_Order" set up in "Form_Current" and I would also like to have the exact same thing for "Parts_Required".
I think I'm actually talking about the same problem, but I'm not too sure, I'm very new to this software.

One other question, and this is embarrassing as I literally just got this code from this website and inserted it into my form, but what is the difference between having "Me!" and "Me." ?


	Code:
	Private Sub Form_Current()
 If Me.Parts_On_Order.Value = -1 Then
  Me!PO.Enabled = False
  Me!Parts_ETA.Enabled = False
  Me!Parts_Notes.Enabled = False
 Else
  Me!PO.Enabled = True
  Me!Parts_ETA.Enabled = True
  Me!Parts_Notes.Enabled = True
End If
End Sub

Private Sub Parts_On_Order_Click()
 If Me!Parts_On_Order.Value = True Then
  Me!PO.Enabled = True
  Me!Parts_ETA.Enabled = True
  Me!Parts_Notes.Enabled = True
 Else
  Me!PO.Enabled = False
  Me!Parts_ETA.Enabled = False
  Me!Parts_Notes.Enabled = False
  Me!PO.Value = Null
  Me!Parts_ETA.Value = Null
  Me!Parts_Notes.Value = Null
End If
End Sub

Private Sub Parts_Required_Click()
 If Me!Parts_Required.Value = True Then
  Me!Part_Numbers_Description.Enabled = True
  Me!Parts_Required_By.Enabled = True
 Else
  Me!Part_Numbers_Description.Enabled = False
  Me!Parts_Required_By.Enabled = False
  Me!Part_Numbers_Description.Value = Null
  Me!Parts_Required_By.Value = Null
End If
End Sub

Thanks for any help you can provide,
~FOX~

I am having a problem trying to find VBA code to import an excel 2007 file into my access 2007 dao database. My problem is that with each excel record I need to create 2 different records which will be written into different tables in my database.

Hello,
I have an Access Database that is used to track stock that is printed.

The first step is an "issuance" in which the amount of stock requested, the stock's item code and some other details on the stock. This is inputted into a RF Scanner, once the issuance is complete a form automatically prints in the warehouse where the stock is kept. The form lists the information the user entered in the issuance screen.

Once the stock is given to the print area, and the printing is completed the print operator enters the processing screen on the scanner. They scan the issuance number that was assigned in step one and printed on the form. Then they enter information about the printed stock. There can be anywhere from 2 on for the amount of records entered into the table in this step. The new records reference the primary key (FLDUID) of the issuance from step one in a field called: FLDISSUANCELINK.

There are other steps performed after this one but it is not in relation to my problem...

I have been working on the front end of this database to allow a print supervisor to go into the records and edit/delete any mistakes the print operator may have made. Obviously I do not want to give the end user access to the table, so there is a form in which he/she may scan the bar code and choose if it is an original issuance or a reprint issuance from a drop down box. However in creating the delete query and vba code, somehow it is not working properly. The strange thing about it is that it will tell me the number of records it is supposed to be deleting, but only delete one.

Below is the vba code that I have on the form, the first one is for when the visibility goes away from the drop down box to update 2 hidden fields on the form. The second is for when the "delete" button is pressed.

	Code:
	Private Sub iss_type_LostFocus()
Me.Letter = CStr(Me.iss_type.Column(0))
Me.FldIssuance = DLookup("[FLDUID]", "TBL", "[FLDPROCESSCODE]= '" & Me.Letter & "'" & " And [FLDISSUANCENUM]= '" & Me.IssNum
& "'")
End Sub


	Code:
	Private Sub Del_Data_Click()
Dim Nbr As Integer
Dim MyIssuance As String
DoCmd.SetWarnings False
    If Me.IssNum.Value = "" Then
    Beep
    MsgBox "Please enter an issuance number", vbExclamation, "Enter a number!"
    ElseIf Me.iss_type.Value = "" Then
    Beep
    MsgBox "Please select an issuance type", vbExclamation, "Select a type!"
    Else
    Nbr = DCount("[FLDISSUANCELINK]", "TBL", "[FLDPROCESSCODE]= 'E'" & " And [FLDISSUANCENUM]= '" & Me.IssNum & "'")
    MyIssuance = DLookup("[FLDISSUANCENUM]", "TBL", "[FLDUID] = " & Me.FldIssuance.Value)
    DoCmd.OpenQuery "Delete Data"
    Beep
    MsgBox Nbr & " Records have been successfully deleted." & vbNewLine & "Issuance Number: " & MyIssuance & vbNewLine &
"Issuance Type: " & Me.Letter, vbInformation, "Success!"
      
    End If
    Me.IssNum.Value = ""
    Me.iss_type.Value = ""
    Me.Letter.Value = ""
    Me.FldIssuance.Value = ""
End Sub

This next code is the SQL Code from the Delete Query

	Code:
	DELETE TBL.FLDPROCESSCODE, TBL.FLDISSUANCELINK, *
FROM TBL
WHERE (((TBL.FLDPROCESSCODE)="E") AND ((TBL.FLDISSUANCELINK)=[Forms]![Enter Issuance Number To Delete
Transaction]![FldIssuance]));

Oh and by the way the "E" is keeps referring to is the transaction type for the print area.

A while back I created an Excel Macro that converts a plain spreadsheet we commonly get into a format that makes it easy to track items and removes all the columns I dont use. Well recently people want that macro from other organizations, and since I cannot figure out how to explain to them how to set it up I created an Access database that would import the document, and export it with all my nifty modifications. Only problem is, my VBA code from Excel is not working right in Access. I have discovered a way around it for most lines of code and I am going line by line fixing it, but one line will not work no matter what I try. It is a simple Replace function, the dates from the original document are stored as text and have "-" in them; I simply want to replace the "-" with a space " " but the same code that works great for this in Excel is not working in Access. I am using Access 2007, here is my code:

	Code:
	Private Sub Export_EPR_Tracker_Click()
Dim FreshFile As String
FreshFile = FileOpenDialog
Dim dAt As String
dAt = " " & Format(Now(), "dd-mmm-yy")
Dim pA As String
pA = "C:Usersnathan.mitson.AVIANODocumentsMS Practice StuffEPR TrackersEPR Tracker" & dAt & ".xlsx"

DoCmd.TransferSpreadsheet acExport, 10, "Alpha Roster Import", pA, True
Dim xlApp, xlBook As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.workbooks.Add(pA)
Set xlSheet = xlApp.workbooks.Open(pA).sheets(1)
xlApp.Visible = True

    Dim EPRDue1 As Variant
    Dim EPRDue2 As Variant
    Dim Val1 As Variant
    Dim Val2 As Variant
    Dim Val3 As Variant
    Dim x As Variant
    Dim LResult As String
    
With xlApp
.Application.sheets("Alpha_Roster_Import").Select
    a = xlApp.Application.WorksheetFunction.CountA(xlApp.Range("B:B"))
    x = 2
    .Columns("C:C").Select
    .Selection.Cut
    .Columns("A:A").Select
    .Selection.Insert Shift:=xlToRight
    .Range("C:E, G:W, Y:AA, AC:AF, AH:AI, AK:AM, AO:BH").Delete
    .Columns("G:G").Select
    .Selection.Cut
    .Columns("E:E").Select
    .Selection.Insert Shift:=xlToRight
    .Columns("H:H").Select
    .Selection.Cut
    .Columns("D:D").Select
    .Selection.Insert Shift:=xlToRight
    .Columns("G:G").Select
    .Selection.Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove
    .Columns("G:G").Select
    .Selection.Insert Shift:=xlToRight, _
        CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("D:D, F:F, I:J").Select
**.Selection.Replace What:="-", Replacement:=" ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    .Range("G1").Value = "DUE TO FLT"
    .Range("H1").Value = "DUE TO SQ"
    .Range("H2:H" & a).Formula = "=RC[1]-30"
    .Range("G2:G" & a).Formula = "=RC[2]-45"
    'Do
        'Val1 = xlApp.Range("D" & x)
        'Val2 = xlApp.Range("J" & x)
        'Val3 = xlApp.Range("F" & x)
        'EPRDue1 = Val1 - Val2
        'EPRDue2 = Val1 - Val3
        'If EPRDue1 > 120 And EPRDue1 < 364 And EPRDue2 > 120 Then
        '.Range("I" & x).Value = xlApp.Range("D" & x) - 30
        'End If
    
        'x = x + 1
    'Loop While x


Not finding an answer? Try a Google search.