I need to use DMax to open the form on the last record based on the serial number. This code is what I have to open the form currently, but I want only the latest record related to the serial number:
I have a form - purchase, and a subform purchase detail they are linked with a field "puid"..I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.
I'm trying to open a folder based on a BIN nr. This folder could be in a main folder that has diferent subfolder. As there where differnt naming used to create the folderes, one of the things they have always is the BIN nr. It alwasy start with a unique number and maybe I could use it to scan the subfolders and open that one that the BIN nr is equal as in the field BIN.
Now we have serveral 1000th of folders and finding them takes time.
I've attached screenshots of the table relationships and some nested forms that I need to discuss in my database.
If you look at the forms screenshot you'll see I have a main form "business/cmc issues" that uses a combo box to select a business name; nested into that I have a second form "policy issues log" that holds details of policy issues about that business; then inside that I have a sub form "issue updates" that records brief details about the actions carried out in trying to resolve each policy issue.
The same policy issue can affect more than one business (because of a relationship between the two companies etc) but still needs to be viewed separately. So for example in the business selector combo box I might have business "ABC". In the policy issue it might say "doesn't pay on time". The "doesn't pay on time" issue might also apply to business "123" and so if I picked that business from the combo box you'd see the same policy issue.
Because it's the same issue for two separate businesses, the actions carried out will be the same, so what I want to do is, after a new action is carried out (where relevant) to be able to click a button that would run some code that copies the actions entered in the sub form for business ABC and pastes them into the sub form for business 123 where the product area and policy issue are equal. This is to avoid having to enter the same data twice.
after I insert a new record using INSERT INTO and then use DMax() (in a private sub) to look up the new record. About half the time DMax() pulls the new record based on the primary key field (AutoNumber) just fine. However, half the time it pulls the max record prior to the new record being inserted. I.e. record 1001 was added and DMax() pulls record 1000. I'm assuming that my issue has something to do with the timing of when the record is writen/saved in the table. Is there a simple method of refreshing the table that I can use prior to using DMax()?
How do i use DMax() function instead of Autonumber, since the table is empty and it wont know what to do with Dmax() + 1 for next record?
E.g I have a new table Customer_details which contains
customerID customerName Age
now if i enter data through forms, i want the CustomerID to be incremental for each new record, so i can use Dmax() function. But since the table is empty how can i tell the form, through VBA that if it is null store 1 else store Dmax("CustomerID", "Customer_Details") + 1
I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.
Tbl_treatment: ID=numeric field (also in tbl_pt and in tbl_tumor) Tumornr=numeric field (also in tbl_tumor) Treatmentnr=numeric field
[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:
However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:
In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.
txtDate is a Date/Time field in the table FlightLog
frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.
I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table.
Ok I'm building a Text Search box that "Live Filters" the results in a ListBox control on the same form.
After typing in a value (Say, for Customer First Name like "Steven") I want the code to do the following:
1. If there is no value in the listbox that matches when the user presses the enter key....open the New Customer form - THIS WORKS
2. If there is a single value in the list, then open this record in the customer form when the user presses the Enter key Receiving a Syntax Missing Operator error on this line:
However, I use this exact code on a button elsewhere in my project (NOT within an IF Function) and it works perfectly fine!
Code: Private Sub txtSearch_KeyDown(KeyCode As Integer, Shift As Integer) If KeyCode = vbKeyReturn Then If Me.ListCustomers.ListCount = 0 Then CmdNewCustomer_Click
I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items.
My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. How to force the combobox to requery through code in a module?
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen FROM voorwerp (inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp) inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau where rubriek in (select rubrieknummer from rubriek where rubriek = variable name ) group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
How do you write complex nested IF THEN ELSE END IF statements..How do you know what IF statement to put first?Then how would you know where to put ELSE, or END IF?Before you start with the VBA, what are the foundations for making it work successfully, know where you are, know where to put END IF etc?
I've seen many times when using VBA people set up an initial foundation for writing code. For example when needing to put something in quotes, they do "" then enter the text in between, so they don't get errors on running (A very simple, crude example I know, but is there a similar basis for nested IFs).
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.
I want a user to enter a number into an input box and then based on the number in this box a form will be opened with records associated only with that employee number. (All employee numbers are stored as text)The code below opens the form, but it is blank.
Private Sub cmdOpenAddKeys_Click() Dim EmployeeNumber As String EmployeeNumber = InputBox("Please Enter Employee Number:") DoCmd.OpenForm "frmAddKeys", acNormal, , "[Forms]![frmAddKeys]![empno]=" & EmployeeNumber End Sub
I have a list box that displays records. When i double click a record it opens the form needed.However I want it to open the form but default to a specific tab. But when it goes to the tab, the subform within it needs to match the ContractID of the record they double clicked on in the search form?I currently have this which opens the Client Form based on the ClientID of the listbox query search results and the client id of the main record.
Code: Private Sub SearchResults_DblClick(Cancel As Integer) If CurrentProject.AllForms("frm_Clients").IsLoaded Then DoCmd.Close acForm, "frm_Clients" End If DoCmd.OpenForm "frm_Clients", acNormal, , "[ClientID] = " & Me.SearchResults.Column(0)
So in theory If doubleClick then open Client Form on Tab3 where subform Contract field ContractID is matches the ContractID in on the search results?
I have a form that is opened as hidden when my startup form loads. Data gets entered into it from other forms so whenever I switch records the hidden form needs to be opened to the matching record.
To accomplish this, I have been using the DoCmd.OpenForm in the OnCurrent event with the WHERE clause matching the PK/FK. I have had no luck with the SearchFor method.
It seems silly to have to keep reopening a form to go to a specific record when the form is always open already. Plus, I am wondering if it is affecting the forms' load times.
I have searched for bits of code to copy other people better than I, and had some success but I am failing to complete, so it is now time for me to visit you all. I am trying to open a PDF from a button on a subform. The location of the PDF is specified in a field. I want to open the PDF and search for a Member ID criteria also listed in a field on the Form.This is my code:
Code:
Private Sub Text12_Click() Dim strPath As String Dim Searchmem As String strPath = [Notetxt] Searchmem = [MemID]
[code]....
Adobe Reader reports "There was an error opening this document. The file cannot be found." but then opens the file (that's something).So it's the "search" bit that is the problem, and I cannot for the life of me get my search parameter to be used.
I tried Link model "bank_pay" with the main_form << to add details of information >> by "pay_num" in main_form and "num" in "bank_pay" by filter >>
Code: Private Sub pay_type_AfterUpdate() Select Case pay_type Case Is = "1" DoCmd.OpenForm "bank_pay", acNormal, "", """[forms]![main_many_1]![main_many_sub]![pay_num]="" & [num]", , acNormal
I am trying to load a form based on an if statement. I think my issue is that I have the DB set to Display form "frmSplash" on open. I have tried the following (frmSplash form load event) but it continues past the frmMenu and stops at the frmSplash. I want to open the DB and look to see if it is registered and if yes then open frmMenu. There is 1 record in tblRegistration so it should open to frmMenu. I checked and it is seeing the 1 record.
Code: Private Sub Form_Load() Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM [tblRegistration]") If rs.RecordCount > 0 Then
[code]...
Does the display form on open override all? how to achieve to goal?
I'm trying to lock a checkbox when the forms open and depending if one has role as admin, it should unlock it.When I add the below code in the on open form I get error:
