Updating A Table Field In A Form Using Public Function?
Nov 13, 2014
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
View Replies
ADVERTISEMENT
May 31, 2014
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database
Option Explicit
Public Function getCurrentRepDates() As Date
Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
View 5 Replies
View Related
Apr 5, 2008
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.
View 7 Replies
View Related
Oct 2, 2004
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
View 1 Replies
View Related
Feb 13, 2015
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?
View 10 Replies
View Related
Sep 13, 2013
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
View 1 Replies
View Related
Jan 14, 2008
Hi all,
I'm trying to get a public function to work within the design view of a query. The function is defined as:
Public Function Percentile_(fldName As String, _
tblName As String, p As Double, _
Optional strWHERE As String = "") _
As Double... ( I can put the whole code if u need it)
Any thoughts about how I can make the Percentile function calculate values according to a GroupBy statement and not for whole data set?
View 14 Replies
View Related
Oct 4, 2013
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String)
If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1
Else
End If
End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then
If Forms(varFormName)!Form.Controls(varContactControl ) = True Then
Etc.
View 4 Replies
View Related
Jun 4, 2014
I have an issue with this case select below. The DelayStart is time so lets say I put in the debug window
?DatePart("h", #04:00pm#)
The result would be 16 which is correct BUT the issue is my second shift starts at 04:01pm and the result is still 16 untill 5pm. How do I fix this so I get the correct shifts? Im guesing use something other than DatePart but what?
'Daylight 6:00am - 4:00pm
'Afternoon 4:00pm - 2:00am
'Midnight 2:00am - 6:00am
Code:
Public Function getShiftForRecord(DelayStart As Variant)
On Error Resume Next
Select Case DatePart("h", DelayStart)
Case 6 To 16 'Daylight 6:00am - 4:00pm
[Code] ....
View 11 Replies
View Related
Oct 29, 2007
Hi All
I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.
Using the function in forms/VBA works fine.
But my DB crashes whenever I try to use it in a query.
My modules code:
= = =
Option Compare Database
Public Type EvCheks
EvType As String
EvAttCat As String
EvUnitAss As String
EvCheksAll As String
End Type
= =
Public Function getEvCheks(EV, EvUnit) as EvCheks
'Event Type: Event or DL
If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then
getEvCheks.EvType = "Y"
Else: getEvCheks.EvType = "N"
End If
'Event Attendance Category: INDB= in database or LIST
If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then
getEvCheks.EvAttCat = "Y"
Else: getEvCheks.EvAttCat = "N"
End If
'Event Assessing Organisation
Dim AOROName As String
Dim AOName As String
AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit)
AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)
Select Case AOName
Case "NA"
getEvCheks.EvUnitAss = "N"
Case "ABCD"
getEvCheks.EvUnitAss = "Y"
Case Else
getEvCheks.EvUnitAss = "X"
End Select
getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss
End Function
====
On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box
MsgBox getEvCheks(EV, EVU).EvCheksAll
This works fine.
=====
In a query I have this SQL code:
SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails
FROM tblEvUnits;
This causes the db to crash.
I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".
I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.
Any/all assistance much appreciated.
Regards
AlanM
View 3 Replies
View Related
Jan 23, 2014
I have built an access application that contains a set of buttons along the top of every form that serve as navigation.* These buttons each perform the same function on every form they are on. (menu opens the main menu, etc) I have database macros to assign each button the same function but I still have to go through each form and manually assign them. I was wondering if it was possible to define a public function that on db open will look for all buttons with a certain name and assign them the macro. (so all buttons called cmdmainmenu will have the OpenMainMenu macro assigned and so on).Before you go there, I have already tried the navigation form and set all forms as subforms.
View 5 Replies
View Related
Sep 23, 2014
Question for Documentation purpose: Should the Public Type be declared in its own module?
Or should it be declared in a standard module where non-public functions use it? It is not for a Form module use.
For a Rule Engine, a function is calling one record on 4 different SQL Views (as linked tables) that have the same field format.
For speed, the recordset should only be opened once. However, there are multiple values that must be returned to the result table multiple fields.
One way to return multiple values is an Array. That has over head too.
Another way is to create multiple public variables. Not my choice for documentation. Another is to create a string.
This is a pure code module with several non public functions / subs. What is the documentation preference? List a Public Type close to the function, or place it in the Global module?
Background: A function can only have one return value.
By creating a public Type, multiple values can be returned.
Code:
Public Type Income
Wages As Currency
Dividends As Currency
Other As Currency
Total As Currency
End Type
Use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:
Code:
Function GetIncome() As Income
GetIncome.Wages = 950
GetIncome.Dividends = 570
GetIncome.Other = 52
GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
End Function
To use the function, you could type into the Immediate Window:
GetIncome().Wages
(Note: the use of "Public" in the Type declaration gives it sufficient scope.)
Important Notice The way this function is called will work, but is wrong from the aspect it re-calls the recordset over and over.
See the proper way to use it submitted below.
View 5 Replies
View Related
Oct 22, 2013
I am designing a Public Function F()
I want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest
We assume that the table tblTest has three fields : TestID , FName and LName .
We also assume that there are already some records in the Table tblTest .
If the function F() is already programmed it should take as arguments as it follows :
F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)
In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.
The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -
I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .
In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????
I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .
View 4 Replies
View Related
Sep 17, 2013
I created a Public Function called fNetWorkDays in my access database, however, when I try to use it in an expression I get "Undefined function 'fNetWorkDays' in expression". The Public Function is in a standard Module in my vba project. Why I cannot call it in my queries?
View 3 Replies
View Related
Dec 5, 2004
Greetings! This is my first post.
I have two tables: 1) tblClient, 2) tblCase.
Client records from tblClient contain a field called Client_CID (Primary Key), as the Client ID. There are also fields Client_HIGH_FILE_NO, a numeric value of the last case number assigned to the specific client and Client_PREFIX that contain a unique three letter prefix that identifies the client.
Case records from tblCase contain a field called Case_CFN (Primary Key), as the Case File Number. tblCase also contains a field called Client_CID that contains the Client ID associated with the case (obtained from a combo box lookup from tblClient).
My form is frmCase bound to tblCase.
The Case_CFN is constructed by combining the value of the selected Client_CID’s Client_PREFIX with the value of Client_HIGH_FILE_NO plus 1.
I am constructing the Case_CFN on the before update event of the combo box for selecting the client. The resulting Case_CFN may appear as follows
ABC10001
Where Client_PREFIX = “ABC” and Client_HIGH_FILE_NO = 10000
Now, I need to increment Client_HIGH_FILE_NO in tblClient by 1, meaning I need to set the value of Client_HIGH_FILE_NO for the selected Client_CID to, in this example, to 10001.
Questions:
1) Is anyone familiar with this type of number scheme generally and if so any ideas?
2) Can anyone tell me how to update the value Client_HIGH_FILE_NO for the selected Client_CID?
View 1 Replies
View Related
Dec 23, 2014
I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])
This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.
View 5 Replies
View Related
Dec 23, 2013
I have the following function that I found online. Unfortunately I can't remember where I got it since I've had it for a little while. Today, when I tried to actually put it to use it didn't work.
I'm calling it from a form as follows: CompactDB (tblHotword)
tblHotword is just a random table from the back end. My understanding of the function was that it would use that table to connect and get the file name of the back end.
Whenever I run it, Access pops up a window that says "Object required" and nothing else. It doesn't look like a standard error message popup. When I click 'OK', Access continues with the rest of the code as if nothing went wrong. The function doesn't run though.
Code:
Public Function CompactDB(TableName As String) As Boolean
On Error GoTo Err_CompactDB
Dim stFileName
DoCmd.Hourglass True
stFileName = db.TableDefs(TableName).Connect
[code]....
View 8 Replies
View Related
Jun 11, 2013
I have multiple reports that use similar IIF statements as the controlsource for four textboxes. Naturally, I don't want to have to update twelve controlsources if any of the calculations change, so I thought I'd make this a public function. However, I don't know how to pass along multiple textboxes as variables. Here's what I have so far:
Code:
Option Compare Database
Public Function txtColor(ByRef textbox As Control)
Dim str1, str2, str3, str4 As String
'The IIf statement is simplified for this example. It's not important.
str1 = "=IIf(IsNull([Inquiry start date]),'W',IIf([txtInquiry]<1 And IsNull([Inquiry end date]),'R'))"
[Code] ....
And this is in the report (where ??? is what I'm asking about)
Code:
Private Sub Report_Load()
Call txtColor(???)
End Sub
The error I get is "Runtime 424 Object Required"
View 7 Replies
View Related
Feb 26, 2014
I am using a public function to feed a variable string to a query. So far I have got:
Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
View 7 Replies
View Related
Oct 31, 2005
This is my first post, but I've been lurking for sometime. I'm grateful for all the great advice given here; despite my efforts, I can't find anything directly related to what I'm doing, though.
I have a form that is populated from a query. The query has some calculated fields and some direct selection fields from a couple of tables. One of the direcly selected fields is one that I'm trying to populate from the items in a list box.
On this form, there are two list boxes, List1 and List2. The user makes selections in List1 and clicks a command button, which runs code so that the second list box is populated with the items from List1. This was shown here:
MS Article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp)
I actually just used this code and made changes accordingly so that this feature is working perfectly. However, the items in List2 need to be updated into a table's field, and this is where I'm having the problem.
I've got List2 bound to the proper field in the query, and I can manually run that query and make changes in that field fine. What I can't figure out is why I can't get the ItemsData property of the List2 control in there. For ease of code, I've added a line that copies the List2 rowsource variable to another variable so that the values can be used elsewhere. I can't seem to get the field to receive the variable in VBA, and I can't figure out how to get the values back into the query so that the query's source table is updated.
Any clues? Or is this unclear? I'm happy to give any further information. I've been working on this for more than a week, trying different things, and I'm at wits' end.
View 3 Replies
View Related
Apr 13, 2013
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 Related
Oct 7, 2005
Table Field not accepting data from the Form function, count.
On the Form with the properties displaying the field, in the Control Source I have the following formula; "= count([OccupantName])
Now this does calculate properly except that the data in not saved to the table field.
Is the above count statement wrong?
Or does this need to be done another way? If so how?
Does anyone know of another way to have the computer count information on a Form that can be saved to a table field?
View 2 Replies
View Related
Feb 12, 2015
I have a main form and a subform.
Both forms have the field called JobID in common.
Both forms have a field called JobStatus.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
View 3 Replies
View Related
Jan 28, 2007
Dear all,
I have a simple database with few tables for which I created forms for the user to enter data.
I craeted in one ofthe forms a ombobox that reads from a table (list of suppliers fro example). I want the user to be able to select a supplier name and then this selected name will be fed to another table (order form for example). I did all requested work as I could understand biut the end results is that it writes only the corresoponding ID number (e.g. 3) of the supplier and the supplier name itself (text).
Am I doing something wrong or missing a code?
appreciate helping a newbie
regards
View 3 Replies
View Related
Jun 19, 2005
I have some data in a field with text type. I want to update some data in the field basing on a criteria by adding text to existing field by using update queries.For example suppose I have "rs" in the field . I want to add "D" to the field then it becomes "rsD". pl. tell me how to do this? or which functions should be used in the queries.
View 4 Replies
View Related
Jun 27, 2005
I have an access table call "Department" and two fields calls "DSec" and "DCode"
like this
DSec --------DCode
50 --------70.1587
60 ---------80.6987
80 ---------60.8521
70 ---------50.1512I like to make a query so that
DSec -------DCode
50 --------50.1512
60 --------60.8521
80 -------80.6987
70 -------70.1587Please Help
Thanks
View 14 Replies
View Related
