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:

	Private Sub tickrelease_AfterUpdate()

    If Me.tickrelease = True Then
    Me.lblrelease.ForeColor = vbGreen
    Me.lblrelease.Caption = "Can be released"
    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.

Sponsored Links:

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?


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
Aqua_tablet = False
End If
If a - 4 >= 0 Then
a = a - 4
Boiling = True
Boiling = False
End If
If a - 2 >= 0 Then
a = a - 2
Clorination = True
Clorination = False
End If
If a - 1 >= 0 Then
a = a - 1
wFilter = True
wFilter = False
End If
End Sub

Private Sub Boiling_AfterUpdate()
If Boiling = True Then
Water_Treatment.Value = Water_Treatment.Value + 4
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
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
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
Water_Treatment.Value = Water_Treatment.Value - 1

End If

End Sub

Can anybody help me with this ?
Thank you in advance


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.

	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)
            lngControlIndex = lngControlIndex + 1
            Me.Controls("Label" & lngControlIndex).Caption = Combo8.List(lngIndex)
        End If
End Sub


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.


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


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.


	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" 
        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........


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.


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?


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.


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.

	EventDefinition = Control.Properties("")

or a bit more real life:

	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?

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 .

	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


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

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.

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

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 X: X = 0
TempCode = ProdCode

If Me.ProdCode = 1 Then

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


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!Stock = TempQty + NEWPURQTY
TempQty = 0

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

End If
Next X

End If



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,

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.


This is the code I'm using ...

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
   Exit Sub
End If
If Len(Nz(TransactionDate, "")) = 0 Then
   MsgBox "You must enter a Transaction Date", , "Input Error!"
   Cancel = True
   Exit Sub
End If
If Len(Nz(UserName, "")) = 0 Then
   MsgBox "You must select a Username", , "Input Error!"
   Cancel = True
   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
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.CC, "")) = 0 Then
   MsgBox "Please select a CC", , "Input Error!"
   Cancel = True
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.Dept, "")) = 0 Then
   MsgBox "Please enter a Department", , "Input Error!"
   Cancel = True
   Exit Sub
End If
End Sub


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.

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 ,


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:


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:


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?


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



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:


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

I need to get this done ASAP.