Forms :: DMax Criteria - Creating A Form To Input New Return Parts Into Database
May 22, 2013
I am currently creating a form to input new return parts into a database. I am trying to automatically generate a tracking number (##-AA-####-####). I have gotten myself to generate the ##-AA-#### in a list box and almost was able to generate the sequence number, 0001, 0002, etc. using the dmax function. I would like to generate the sequence number one higher than the highest, depending on part type and last 4 digits of part number. Our parts have unique last 4 different p/n but more than one can fall under the same type.
View Replies
ADVERTISEMENT
Oct 11, 2013
I'm using the following code to generate a sequential number"
Code:
Private Sub Form_Load()
Me.txtReceivedDate = Now()
'assign an EmailID when form loads
Dim CurMax As Long
Dim NewMax As Long
'poll the current EmailIDs for the largest number from current year and add 1
[Code] ....
Until recently, I didn't have any criteria on the DMax and it worked perfectly. It's now been decided that the EmailID should reset back to 1 when the calendar year changes.*
*EmailID is not my primary key - There is a separate autonumber field used as the primary key.
The table I'm using the DMax on has a field named ReceivedDate which stores a Now() when a record is created. The form I'm working in has a textbox named txtReceivedDate that is set to Now() when the form loads as can be seen in the code above.
What I'm trying to use as my criteria in the DMax is to match the Year() of ReceivedDate in tblEmails to the Year() of txtReceivedDate on the form.
For 2013, the current max (CurMax) EmailID is 21. If I set my computers date back to 2012 (where the CurMax is 3) the txtEmailID is still being populated with 22, even though txtReceivedDate shows a 2012 date.
I've tried a handful of permutations of the criteria string:
Code:
Year([ReceivedDate]) & " = " & Year(Forms!frmNewEmail!txtReceivedDate)
Year([ReceivedDate]) & "=" & Year(Forms!frmNewEmail!txtReceivedDate)
Year([ReceivedDate]) = Year(Forms!frmNewEmail!txtReceivedDate)
I've tried a few others long the way that I don't even remember, but there's probably little value in listing all the ways that don't work.
I've got a syntax issue within the criteria since it's the first time I've tried to use one and it contains a couple moving parts.
Once I get this sorted out a follow on question is about dealing with the users changing the txtReceivedDate to a different year. As of now, I have the same code (everything except "Me.txtReceivedDate = Now()") also present in txtReceivedDate_LostFocus(), but I'm not sure that's the best place for it.
View 5 Replies
View Related
Mar 28, 2015
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
View 1 Replies
View Related
Jun 24, 2013
I am fairly new to Access and my "changed" position at work requires that I learn much more about the software. My first challenge is to learn how to make an existing form prompt a user to confirm or cancel changes. I don't know anything about coding but I searched online and found some coded that is supposed to make this happen. I went to "form properties'' and typed this (below) in BeforeUpdate:
'If the form data has changed a message is shown asking if
'the changes should be saved. If the answer is no then
'the changes are undone
On Error GoTo BeforeUpdate_Error
[Code] ....
BeforeUpdate_Exit:
Exit Sub
BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
After saving changes to the design, I tested by changing the record. I received no prompt.
View 2 Replies
View Related
Sep 18, 2014
I always have a problem with input forms in Access in that I usually end up with an empty excess record which is being created because I do not know how to program this correctly.
I have a Purchase Order Receipt form (frmPurchase). When I receive a certain quantity of an article associated with a lot number (or a serial number) I have another input form opening up (frmLotorSerial), passing to it through Openargs, the article and the quantity received.
Say we receive 10 units of which we want to register the serial number, I want the user to be able to enter a maximum of 10 units (could be 10 records if each record = 1 serial number, but it could be less than 10 if we have several units per lot number), and not one more, into this frmLotorSerial.
The record looks like this:
-key
-artno (article)
-recqty (quantity received)
-date
-warehouse
-lot number
I used the lost_focus event on lot number to check whether the total received quantity in this form matches the total passed to it from the Purchase form, and if it does to stop the input. Since I could not close the window in the code of this event (error 2585) I moved the close command to the current event of the form.
Doing this closes the form all right, but it always creates one record extra, which of course does not carry any information, apart from the date which has a default value of now.
How should I program/what events should I use to:
-test that the user should not go any further (total received in frmLotorSerial = total received in frmPurchase)
-close the window and, above all, not create this extra record.
View 2 Replies
View Related
Jan 18, 2006
Hello,
I am using DMax() to return the highest date in a table, but really need it to return the second-highest...so, if I had:
14th Feb
10th Dec
1st Jan
I don't want it to return 10th Dec, I want it to return 14th Feb
Any help much appreciated. I am sure there was something like dmax()-1, but that just returns (in my example above) 9th Dec!
Thanks!
View 4 Replies
View Related
Nov 4, 2013
I want to create a form to search my parts.example I typed in a part number 123 , the other text box also will come out the detail about the part 123.
View 2 Replies
View Related
May 15, 2014
hiding parts of a form if a option button is selected. There are two option buttons and two parts to the form. I would like each part of the form greyed out or made invisible depending on which radio option button is selected.
View 14 Replies
View Related
Dec 3, 2014
I have a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.
Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.
View 5 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)
Value1(Double)
Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 3 Replies
View Related
Dec 19, 2014
I have a table with the folowing structure:
ID(integer)Value1(Double)Value2(Double)
I need to split this table in 4 equals parts:
- count of id must be equal or close to equal(ex: 467 split in 3 x 117 and 116) for each part
- the cumulated Value1 and Value2 must be equal or close to equal for each part
View 11 Replies
View Related
Nov 4, 2005
Hi
This is simple im sure but i am a thicky
How do you allow a user to enter a value in a form; have access set that inputted value to a criteria in a query? Then ill have a button to run the query which i can do
thanks
thicky ste
View 1 Replies
View Related
Nov 8, 2006
Hello!
Simple one for most, but I have having problems with a Database I am setting up
The database controls stock movement (i.e. inwards stock and outwards stock)
I have set up a Parts Inwards table with the following fields:
Part Number (linked)
Qty In
Stock Location
And I have set up a Parts Out bound with the following fields:
Part Number (linked)
Qty Out
Stock Location
Now for the problem, when I run a query to tell me how many parts I have left in the stock room i.e. Qty In - Qty Out, it duplicates the data in the Qty In.
I assume its got something to do with the fact that I have multiple Qty out’s for a single Qty In entry…hope this make sense?
Can someone help?
View 4 Replies
View Related
May 31, 2006
Based on information from a earlier thread.... I created a Union query that pulls information from multiple tables and fields.
SELECT AG_B_R1 as Num FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R2 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R3 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_B_R4 FROM dbo_ADC_Ag_B_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R1 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R2 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R3 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_m_R4 FROM dbo_ADC_Ag_m_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R1 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R2 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL
SELECT AG_e_R3 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll]
UNION ALL SELECT AG_e_R4 FROM dbo_ADC_Ag_e_Res WHERE Rollnmbr=[roll];
And then I created another query to get the STDEV of the above query
SELECT StDev([Num]) AS StDev
FROM Q_cals_ag_bme_STDEV_Union;
The result will be on a subform on my main page. How do I get my form to input the [roll] automatically and requery the subform, showing my result.
Thanks
View 1 Replies
View Related
Jan 30, 2006
I have this select query.
SELECT DISTINCTROW L160.Date, Avg(L160.Zinc) AS [Avg Of Zinc], Min(L160.Zinc) AS [Min Of Zinc], Max(L160.Zinc) AS [Max Of Zinc], Count(L160.Zinc) AS [Count of Zinc]
FROM L160
GROUP BY L160.Date
HAVING (((L160.Date)=[Forms]![L-160quarterfrm].[Date]));
How do I build a form that would ask the user to input a range of dates for the criteria?
Thanks! :o)
View 1 Replies
View Related
Aug 18, 2015
I have a query that uses the input from a form as criteria, which is then used in a report. The form input is a drop down based on another table. This is a sales pipeline report, and the list is a list of sales people. The report works perfect for all sales people except one. When I run it for the one, I get the following error:
"This expression is is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables".
I DO NOT get the same error when running the query by itself - so assuming there is something in the report causing this. I do have some sum formulas in the report.
Again, no other salespersons selected cause this error -- so I am assuming there is something in the dataset for this person that is causing the error.
View 8 Replies
View Related
Jun 25, 2013
how to restrict access to forms and reports using the Active Directory. It is a pretty simple method to pick up and easy to use in any database. My problem comes when I try to apply this same method to tables and queries as well.
View 5 Replies
View Related
Feb 18, 2005
Note: I put this in General original but it is rmore likely to be a table structure problem
I have a single table for storing the main data, this is fed by input from 3 forms. I have been asked if the forms can be used independantly and remotely as input forms.
My solution was to simply send out a database with just one form and created an append query so that when it is returned with completed information it would simply append the database to the main database. In itself this works fine however, what is happening, of course, is that when I get the other forms back I end up with triplicate records, that is instead of the information from the 3 forms being stored as one record it is now split over 3 records.
I have to be careful in what I do as the original database has been running for over 6 months now and has a lot of stored data and because of established queries/calculations/reports the last thing I want to do is change or split the main data table.
Is there anyway I can get the three records to concantenate on one line. The three forms all have separate fields as they were taken from the original database.
View 7 Replies
View Related
May 24, 2014
I am creating a Machinery database to match parts up with equipment. So they can see what the Equipment is and all the available parts. The sort needs to be like this
First Sort:
If the manufacturer of the machine is the same as the manufacturer of the part sort that at top then sort alphabetical
Is this possible? Each equipment would have a different manufacturer so I can not do some type of hard coding.
View 1 Replies
View Related
Nov 13, 2014
I have a
- form called "Hauptformular" and a
- subform called "qry_tracking_Unterformular". Then there is a
- button called "cb_filialname".
Now when opening the Database, the form appears with the subform containing the
- query "qry_tracking"
When hitting the button "cb_filialname" the subform receives the contents of the
- query "qry_tracking_FilName", that has a filtering option on the Column "Filiale": "Like [Filialname?]"
Now the filtering works fine with the following Code:
PHP Code:
Private Sub cb_filialname_Click()Me!qry_Tracking_Unterformular.Form.FilterOn =Â
FalseMe!qry_Tracking_Unterformular.Form.RecordSource = "qry_tracking_FilName"End SubÂ
-> when you hit the button, the inputbox
Filialname?
appears.
The problem now is, that after having used the button, when I click the "Exit" cross right up there, the inputbox appears again.
View 2 Replies
View Related
Apr 30, 2014
I have a database with 88 tables that have 5 fields each. Each field has the same name in every table. The ideal situation would be to have a single table to hold the data currently stored in the 88 tables but each table holds different information. Each of the 88 tables is related to 3 additional tables that hold header information. Needless to say the header info will be identical for each entry of the 88 tables. Access is limited to 256 fields per form so having all fields in a single form has not worked this far. Splitting the forms into groups of tables somewhat works but the header info needs to be inputed every time and it creates duplicate info. Creating a single form with multiple tabs does not work due to the 256 field limitation.
View 14 Replies
View Related
May 10, 2007
Hi:
Using MS Access 2000.
Maybe I've been looking at the monitor for too long but... I need to have a query return data based on a date field or return all dates greater than 1/1/2001 if left blank.
criteria: [Enter date - mm/dd] Or >#1/1/2001#
When I leave it blank it gives me all dates as it's supposed to but when I type a date it does not filter by that date, I still get everything.
I also tried [Enter date - mm/dd] Or Like "*" but it behaves the same way. I know it has got to be easy but my skull may be too thick to let me see it.
Any help is appreciated.
:confused:
View 5 Replies
View Related
Jun 18, 2013
I have a table, which when i first set it up i decided that a couple of the fields had to be mandatory.
So, i set the "Required" property of the field to "Yes" (at table level - which is probably poor practice?)
I now have a form that allows a user at the front end to enter items in the table.
If they complete all the fields on the form i have used macro builder to save the record, present a nice message and move to a New Record. (3 elements in all)
However, if they try to save the record having not completed the "Required" fields then they get an error message instructing them the field is required, but then the macro crashes.
So what's the best way to approach Mandatory fields? Is it best to leave the "Required" property at table level set to No and then have something at form level which checks they have entered a value in the field? (i'm guessing this is probably the correct approach)
I tried removing the table level condition from the [Field] and adding this as the first argument of the macro builder which saves my record:
Code:
IF [Field] = "" Then
MessageBox "You Must Complete Mandatory Fields"
Stop Macro
End If
But my macro still completes and saves the record...
View 6 Replies
View Related
Feb 10, 2014
I am having problems developing a membership database - I have three main tables.
1. A table of 600 members of an organisation
2. A table of the subgroups these members may join, about 80 in total.
3. A table of the members of each group.
The members do not have a unique ID - complicated reason for this so I use a system assigned ID. Group ID does have a unique Id but I chose to use a system assigned ID.
Table 3 records effectively consists of just two fields, memberID and groupID. When I create a form and subform to enter these values all is well. But I cannot expect users to know these values, so I have been trying to create a subform that creates/lists/removes members from groups, using a Group main form with a member tabular subform with a surname search through a combobox. Groups have between 5 and 20 members.
e.g enter 'smit' in the combobox on the subform and a list of smiths is displayed together with the full name, from which the user selects the correct entry. At this point the record showing for instance, Paul Smith belongs to Group 17 is written to table 3. All sorts of issues arise, too many to document.
View 13 Replies
View Related
Sep 18, 2013
I want to create a list box that contains a list of all the available forms in my database.
View 8 Replies
View Related
Jun 28, 2014
My code is supposed to advance the number of times the current user who is logging in and advance by one. It is NOT doing this by the Current User? It is advancing the count from the greatest number by one?
Need to modify SQL to allow for txtlogincnt ? ? ?
txtUserID is the Users Login Name.
LoginCnt is the number of times the User has Logged in.
1 Other pieces of code that get's the Log Count per User logging in. This part works!
'Get Login Count.
Quote:
Me.txtlogincnt = DLookup("[LoginCnt]", "tblUserSecurity_Sec", "[userID]='" & Me.txtUserID.Value & "'")
Quote:
'Update Login Count
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblUserSecurity_Sec SET tblUserSecurity_Sec.LoginCnt = '" & Nz(DMax("LoginCnt", "tblUserSecurity_Sec") + 1) & "' " & _
"WHERE (((tblUserSecurity_Sec.userID)='" & Me.txtUserID.Value & "'));"
DoCmd.SetWarnings True
View 3 Replies
View Related
