I wrote a program in Access 2003 and it works perfectly on my computer but when I run it in another one , there is a problem.
The problem is : on a form Ioad I calculate a value and I declared it as public. I need to use this value in my form but it does not recognize it. As if public statement does not work.
Is this from the Windows?
Again, it works on my computer but I have a problem elsewhere.
I am working on a database where I want some specific information displayed on a form, preferably on the form header. I want to display this on all of my forms. In a standard module, I have a code segment that runs a query returning values, and I assign those values to my public variables. I run this code segment triggered by "on activate" of each form.
The variables contain an Event Date, and an Event Name. I want this information to appear on each form. How do I get this data onto my form as part of the form header, or if cannot put on header section, then in the detail section?
SQL Query
SELECT COUNT(*)
FROM Orders
WHERE (((Orders.Date) = [forms]![frmOrders]![DTPdicker2]))
VBA CODE
Option Compare Database
Dim iDay As Long
Dim iCount As Long
Private Sub DTPicker2_Change()
iDay = 0
iCount = 0
iDay = Weekday(Me.DTPicker2.Value)
iDay = iDay * 100
'iCount = Count(*) from SQL query above'
iDay = iDay + iCount
Me.txtRefNumber = iDay
End Sub
Basically I am trying to generate a meaningful reference number while adding new orders rather than using an ever growing unique id(AutoNumber), for creating Weekly Reports.
I have tried using the following code
iCount = DoCmd.RunSQL (qryReturnCount)
and I get an error
Any help would be greatly appreciated.
Last question for the gurus out their can it be done with the expression builder? To save me banging my head against VBA syntax.:mad:
Cheers Andy!!!
I'm trying to update a SQL table using a form button, and getting an "Enter Parameter Value" textbox prompt when the value is already declared. Not sure what is wrong. I know the value is valid as I'm returning it in a MsgBox.
Here is my code.
Code:
Private Sub cmd_data_merge_Click()
finish = [finish].Value
conv = DDEInitiate("EXCEL", "123 Staff List.xls")
For x = 4 To finish
cell_employee_number = "R" & x & "C9"
cell_surname = "R" & x & "C8"
[code].....
I need to pull the calculated values from approximately 10 forms into another form. This is a summary form that should have all the totals pulled from the other forms.e.g. Form A has a textbox that reflects the sum of the amount. This is the total balance of form A.Form B, Form C, etc. all have a total Balance.Now, i need to pull all these totals into a summary form
-Form A Total Balance: x
-Form B Total balance: y, and so forth.
How and what is the best method to approach?I have tried using Forms![Formname]![Total] to get the data. This necessitates the need to hide all these forms and I ended up with blank forms, etc.Even so, the total sometimes appear and sometimes it does not. so it is very unstable.
I have a form where the record source is a table.The form has about 5 calculated fields on it. The screen flickers quite badly and at the bottom says calculating. Sometimes the database locks up.
To stop this flickering i created a query so that the calculations are done in the query. Then i linked the form to the query instead of the table. Am i doing this correctly as i didn't think the table would show any new data that i entered into the form but it does seem to work. Is it because i am only using one table.
I am using Access 2003.
I have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.
I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with the new PI Number it was blank.can't figure out what I am doing wrong.
Code:
Public Function NewPINum() As String
Dim vNum As String
Dim strYYMM As String
Dim getnextPI As String
strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then
vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3) vNum = vNum + 1 getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")Else
vNum = "001" getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
End If
End Function
I have a main form with several with four subforms. On the main form I have a bound text box [ShiftTotal] I have on each subform a unbound text box that sums a field [ExtTaxIn]. I am working with just one subform till I can get it to work correctly. The code I am using on the after update event is as follows.
code
Private Sub TxtSoldQty_AfterUpdate()
On Error GoTo HandleError
Me.ExtTaxIn = (Me.TxtSoldQty * Me.TaxIn)
Me.ExtPrice = (Me.TxtSoldQty * Me.Price)
Me.InvSold = (Me.TxtSoldQty * Me.UnitOfSale)
Forms!frmShiftMain!TxtShiftTotal = Forms!frmShiftMain!TxtRunningTotal
Me.txtExtTaxIn.Requery
[code]...
This sort of works but the [ShiftTotal] on the main form is always one table row behind. I think this is because of the order the event fires.
I have a form with three fields named 'quantity' (numberfield), 'service' (combo box) and 'amount' (calculated field with controlsource =[service].[Column](2)*[quantity].
I try to sum the amount field in the formfooter with =Sum([service].[Column](2)*[quantity]) but I receive an error.
Somehow it does not get the value for [service].[Column](2)
How can I do this?
The table for the form is called customer_detail
Here is the basic info:
Table has Checkbox column
BusinessDate column (mm/dd/yyyy format)
OrderTotal column (in dollars)
There are others but these are the relevant ones
In the header of the form I have a calculated control box with the following control:
=Sum(Abs(nz([Checkbox],0))*nz([OrderTotal],0)),0)
Which works just fine. The user is presented with a list of all the orders from this table in the form. They check the checkboxes and the control in the header shows the total of the records checked. I need to make 7 of these calculated controls, one for each day of the week. Is this possible? This way the user can see the order totals for each day of the week they have selected.
I tried:
=IIf(Weekday([BusinessDate])=2,Sum(Abs(nz([Pull],0))*nz([OrderTotal],0)),0)
but it still sums up the whole week as the expression holds true.I do not know VB, and I'm sure there is a really neat and easy way to do it in there. I just don't know how.
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?
View 1 Replies View RelatedIn my DB i would like to make a form that displays different calculated values based on other forms and queries like income running cost etc., but i do not know how to refer in an unbound control to value of a control in another form. I get always #names? error. However it worked when loaded that form i refered to. Is there a way to do it without loading the refered form?
View 3 Replies View RelatedI have a form that is displayed continuously and there is a text field in it (Text12).In the footer of this form I would like to add a textbox that is the sum of all Text12's on the form.I tried =Sum([Text12]) in the textboxes control source but that is just displaying an error when I run the form.
View 2 Replies View RelatedWe have a recent issue on client PC. After application running for a while, all the sudden the calculated field on the form not showing the details, though there is data on the control. After restart the PC, the calculated field display correctly. We guess this may be due to the theme we use.
View 3 Replies View RelatedI have a subform embedded in a main form, the subform has a field (text box) that is doing automatic calculation so no manual entries are needed from the user. I have tried to pass automatically the final result to a text box in the main form, but all events are not working (afterupdate, On change, On Dirty), etc.
I have a button to do the above mentioned in the main form, but I don’t want to use that button, I want to do all the process from the subform.
Also, from the subform I was able to do click on the button on the main form (programmatically), however MS Access is too quick and the value extracted from the subform is always empty (zero); Acees is finishing before the calculation is done.
I am using MS Access 2010.
I have created a form that is based on a table.
The fields in the form are currently pull downs or manual enrty fields (all based on the columns of the table... "Bound" I think you call it).
I also have fields that I manually brought into my form as text boxes that I am using to populate a series of numbers. I have named them P1, P2, P3 etc. They are unbound fields.
I have a field that I am totalling the unbound fields. It is a column in my table. At one point the control source name was the table coulmn name.
I have changed the control source to read =[p1]+[p2]+[p3]+ etc.
The total does show up in the field on my form
HOWEVER.... When I save the data the total number does not get brought into the table. All of the other fields do.
Why can't I set a controls default value to a public variable?
View 2 Replies View Relatedi have a continues form that lists all my active items and I'd like to use it to quickly check if they have all the data inserted, ie entries in each separate table. it looks like this
I perform a simple check on form load chk = DCount("[phID]", "preh", "[phbID] = " & Me.tID) where me.tid is the item ID and this gives me 0 or 1 and writes ok, or missing into the appropriate box.Problem is it tkaes value for the first item and repeats it for eah other, so I get ok or missing throughout the continuos form.How can i force a check for each line?
I'm new to VB and am learing slowly with all your great help in this forum.
I'm wondering if its possible to write a Public Function for vbCRLF. I have a field in multiple forms that are all named 'Note'. If I have the following code:
Public Function PubNoteLF()
On Error GoTo PubNoteLF_Err
My!Note = My!Note & vbCrLf
PubNoteLF_Exit
Exit Function
PubNoteLF_Err
MsgBox Error$
Resume PubNoteLF_Exit
End Function
When I run the code I get Compile Error: 'Sub or function not defined.'
Can someone tell me what I'm doing wrong? Its problably something real basic, but I'm stumped. Thanks.
I'm into one of the subforms that will be using the public variable from the main form and am not having success passing the data from the main form over. on the subform, i created a textbox (txt_currentyear) w/ this in the control source field: =[WrkYear] & " Golf Outing"
WrkYear was the defined in a module as:
**********
Option Compare Database
Option Explicit
Public WrkYear As String
**********
I have created a listbox w/ a few years in it (current_year_listbox). this has =[WrkYear]=Me.Current_Year_listbox.Value in the after update field. i loaded the main form, selected the year, went into the subform to see if it passed the data along w/ no luck.
do i need to call or reference that module in every form or report before i can use the data from it? right now, i just get an empty field on the subform.
Hi all,
I have done a multi user database for a private company (and thanks to all those that helped me out) but now I need to do another database to put onto the Internet so the general public can view it and order the products. Obviously some of the database needs to be private (so I can update records) but some needs to be viewed over the Internet. I would like the general public to be able to view the products over a web page, not download a program to view the database.
I have made the database already so I can pull up a web page (from within access) and order the products - all I need to know is how to do this over the Internet?
I don't expect anyone to hold my hand through this, I just ask if anyone has a link to a walkthrough so I can do this?
Regards, James
PS Have another question -
I have 17, 500 records and I wish to add a new Field, how can I change the values of the Field without going thorugh them one by one? e.g If the Record has 'Rarity = U' then I want the 'price' (which is the new field) to equal 1.
It just occurred to me that I use certain variables in several of my routines. Maybe you've done the same... something like:
Dim db as DAO.Database
Dim rst as DAO.recordset
Dim qdf as DAO.QueryDef
Now, if it's re-used in many routines, wouldn't it make more sense to make it a public variable, and set them to whatever you need it for a certain routine? Would it be more efficient than having to re-initializing and discarding it for every routine that used it? What problem would one expect from making them public?
Also, since it's very common to set db = CurrentDb, would it also make sense to set it publicly, and for cases where you aren't using CurrentDb, use a private variable just for that one?
I haev a report with 15 sub reports. I have to use Visual Basic to format the sub reports. I know how to do the formatting (see below) but what I would like to do is have one function (Public) that I can pass the subReport name to and have the formatting happen. This would be mush easier to maintain than copying the code below into on_print events of all 15 sub reports.
Has anyone done this and have an example??
Thanks
--------------------------------------------------------------------------------------
Private Sub GroupHeader0_Print(Cancel As Integer, PrintCount As Integer)
Dim widthOfBox, startLeftSide
startLeftSide = 0.017
widthOfBox = 0.21
Me.ScaleMode = 1
Me.ForeColor = 0
'Repeat the following line of code for each vertical line
' 1*1440 represents 1 inch
' Me.Line (0 * 1440, 0)-(0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 0 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 0 * 1440, 14400) 'Draws line at Left Margin
Me.Line ((startLeftSide * 1440) + widthOfBox * 1 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 1 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 2 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 2 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 3 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 3 * 1440, 14400) ' Draws next line
Me.Line ((startLeftSide * 1440) + widthOfBox * 4 * 1440, 0)-((startLeftSide * 1440) + widthOfBox * 4 * 1440, 14400) ' Draws next line
'the 14400 is an arbitrary number to increase the line to the max of a
'section.
End Sub
I have a query that I'm working on through Access 2010's design view. I'd like to add a criteria to the query where it only shows results with the employee name column matching a global variable I created that stores the name of the currently logged in employee.
Here's my vba code that declares the global variable and the public function i'm trying to pass as criteria in the query:
Global gbl_loginName As String
Public Function returnName() As String
If IsNull(gbl_loginName) Then
returnName = "test" ' dummy account created for development only
Else
returnName = gbl_loginName
End If
End Function
and here's the SQL code from Access's design view:
SELECT [Entry of Hours].WC, [Entry of Hours].[Employee Name],
[Entry of Hours].[Set Up Time], [Entry of Hours].[Run time],
[Entry of Hours].[Traveler Number], [Entry of Hours].[Entry Date],
[Entry of Hours].[Quantity Finished], [Entry of Hours].Notes, [Entry of Hours].WPS,
[Code] ....
when I try running the query, however, I get this error:
'returnName' is not a recognized built-in function name
Is there a problem with using public functions in Access' design view?