Other subreddits of interest:

I was Manager of Laboratory Information Services for the Canadian subsidiary of a global tobacco company. I started there when I graduated as a Chemical Engineer and worked there for 40 years until my retirement. I was fortunate to find an interesting career with a great company.

Whenever I see a question here in the MS Access forum related to lab databases, I try to answer in a way that takes into account the unique requirements of laboratories as well as the technical requirements of database design. Recently, a user commented that my post on developing a LIMS (Laboratory Information Management System) in Access was turning into an AMA – and that gave me the idea to actually do an AMA on handling data in a laboratory environment.

Please feel free to ask any questions related to developing either Access databases or Excel workbooks to capture, analyze, and report lab data – or even about working in the tobacco industry.

This can include using Access to handle internal training requirements and using Excel for statistical analyses, quality control (like control charting and outliers identification), and compliance to ISO Standards (we were accredited to ISO 9001, ISO 17025, ISO 14001, and OSHAS 18001).

I have a query linked to multiples tables all linked together.

query design

I now have a form to display the query. I am trying to get it that the end user is able to change the text (shortages) or check boxes on the form. The records have no locks but am unable to do anything in the form.

Form

Hola soy nuevo en access y quisiera saber cómo puedo hacer que unos campo de uno de mis formularios se refleje en una tabla, en específico unos campos en los que estoy usando formulas para calcular datos, y en la propiedad de origen ya no lo puedo vincular a la tabla.

Hi, I'm new to Access and I would like to know how I can make some fields in one of my forms be reflected in a table, specifically some fields in which I'm using formulas to calculate data, and in the "origin" property I can no longer link it to the table.

I have to be honest, I've never felt 100% comfortable that a complex database won't have some strange error while I am making a lot of changes to it. I can always fix the problem by importing everything into a blank database, but still, it's not a good feeling, I just accept it and move on, and don't usually mention it to the customer. That is why I developed a habit of making changes to a copy of the system, noting each object changed and then importing them into the live system after testing. Is there anyone out there that never gets any corruption? If so, what is your secret.

Hello,

I am working on an assignment and I am supposed to enforce referential integrity. I keep getting the error 'relationship must be on the same number of fields with the same data types.' I have asked my classmates and they are stumped as to why this is happening to me.

https://preview.redd.it/yqjg8ahefc4e1.png?width=561&format=png&auto=webp&s=9feec47a365bd43db316c859c29d0fabfbba6bd2

https://preview.redd.it/58hk8p3gfc4e1.png?width=561&format=png&auto=webp&s=5b29aaa71e16e67eddc316f0f87fcc1ee4296ed3

I tried to make the field size the same, but it would not allow me. I was wondering if anyone had possibly seen this issue before

https://preview.redd.it/g5a5ir61lb4e1.png?width=596&format=png&auto=webp&s=f34dae03cace5500395c1189eb360e53acdff277

Hola quiero hacer eso en acces como sale en la imagen de abajo pero no se como hacerlo

There are two types of rule violations that piss me off the most: spam, and people who delete their posts once they get an answer.

Spam is self-evident. The reason the second type pisses me off so much is because people take the time to help people and reply, and then when someone deletes the post, all of the respondent's work is gone. Not only is their work gone and they don't get a point; but it's as though they spent that time for nothing. Part of the purpose of these responses is so people in the future can also be helped by finding a past post similar to their current situation. But the person who deletes their post (either because they're afraid their boss or coworkers or teacher is going to see, or for whatever other reason) takes that away from anyone else who might benefit from the reply.

Rule 8 explicitly prohibits deleting a post once someone has given a reply. Yet people do it. And since, once the post is gone, so is their user name, it's been hard to enforce that rule.

So I just added the user's name to the copy of the post that's created in the comments when the user creates the post. That name will remain, even if the post has been deleted. And anyone who violates that rule will be banned. (Of course, if people request a ban lifting, and they seem sincere and were unaware of the rule previously, then I usually lift the ban.)

So, if you happen to notice a post that's been deleted after someone commented, please send a message to the mods.

Thanks!

I have an opportunity at work to look at fixing some slow queries and the conversation came up as to our approach to the sql connection as a whole.

A couple of questions here:

1. We currently make a new connection per query as to not hold a connection open the entire time the app is open. Is this good practice?

2. What is everyone’s preferred method for connection? We currently use ODBCs and Linked tables but is ADODBs faster? Or more reliable?

** this is an internal tool if that is helpful

Hi I have a sub form, within a form, which contains combo boxes and text boxes. I want to use it to enter data, creating new records whilst also ideally potentially being able to edit existing records which have been created in the same sitting. I'm not sure which approach to take.
At the moment, I'm using continuous form view, but my code (see below) is playing havoc with the existing records, e.g. CategoryCB_AfterUpdate causes an update of the CategoryCB combo box to requery the Subcategory combo box (SubcategoryCB) resulting in the existing data being cleared.
I'm pretty new to Access and I'd really appreciate guidance on what approach to take please.

Private Sub Form_Load()

' Initially show all subcategories, including CategoryID

Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT ORDER BY SubcategoryName"

Me.SubcategoryCB.ColumnCount = 3 ' Set the column count to 3

Me.SubcategoryCB.Requery

End Sub

Private Sub Form_Current()

' Set the initial state of the CategoryManuallySelected flag

CategoryManuallySelected = False

If Me.NewRecord Then

' Disable the relevant text boxes on form load

Me.MilesTravelledTB.Enabled = False

Me.MonthsUsedTB.Enabled = False

Me.AmountTB.Enabled = False

Me.InvoiceNoTB.Enabled = False

Me.DescriptionTB.Enabled = False

End If

End Sub

Private Sub CategoryCB_AfterUpdate()

' Clear the SubcategoryCB value and filter based on the selected Category

Me.SubcategoryCB.Value = Null

Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT WHERE CategoryID = " & Me.CategoryCB.Value & " ORDER BY SubcategoryName"

Me.SubcategoryCB.Requery

' Clear relevant fields

ClearRelevantFields

' Set the flag to indicate manual selection

CategoryManuallySelected = True

End Sub

Private Sub SubcategoryCB_AfterUpdate()

If Not CategoryManuallySelected Then

' Access the CategoryID directly from the combo box

Dim CategoryID As Integer

CategoryID = Me.SubcategoryCB.Column(2)

' Update the CategoryCB with the corresponding category

Me.CategoryCB.Value = CategoryID

End If

' Enable relevant fields

Me.InvoiceNoTB.Enabled = True

Me.DescriptionTB.Enabled = True

' Update column visibility and clear relevant fields

ClearRelevantFields

UpdateColumnVisibility

End Sub

' ClearRelevantFields subroutine definition

Private Sub ClearRelevantFields()

Me.MilesTravelledTB.Value = ""

Me.MonthsUsedTB.Value = ""

Me.AmountTB.Value = ""

Me.InvoiceNoTB.Value = ""

Me.DescriptionTB.Value = ""

End Sub

Private Sub UpdateColumnVisibility()

Select Case Me.SubcategoryCB.Value

Case 16 ' Subcategory for Miles Travelled

Me.MilesTravelledTB.Enabled = True

Me.MonthsUsedTB.Enabled = False

Me.AmountTB.Locked = True

Me.AmountTB.Value = ""

Case 47 ' Subcategory for Months Used

Me.MonthsUsedTB.Enabled = True

Me.MilesTravelledTB.Enabled = False

Me.AmountTB.Locked = True

Me.AmountTB.Value = ""

Case Else

Me.MilesTravelledTB.Enabled = False

Me.MonthsUsedTB.Enabled = False

Me.AmountTB.Locked = False

Me.AmountTB.Enabled = True

Me.AmountTB.Value = ""

End Select

End Sub

Private Sub MilesTravelledTB_AfterUpdate()

If IsNull(Me.MilesTravelledTB.Value) Or Me.MilesTravelledTB.Value = "" Then

Me.AmountTB.Value = ""

Else

Dim miles As Integer

miles = Me.MilesTravelledTB.Value

If miles <= 10000 Then

Me.AmountTB.Value = miles * 0.45

Else

Me.AmountTB.Value = (10000 * 0.45) + ((miles - 10000) * 0.25)

End If

End If

End Sub

Private Sub MonthsUsedTB_AfterUpdate()

If IsNull(Me.MonthsUsedTB.Value) Or Me.MonthsUsedTB.Value = "" Then

Me.AmountTB.Value = ""

Else

Dim months As Integer

months = Me.MonthsUsedTB.Value

Me.AmountTB.Value = months * 26

End If

End Sub

Hi all,

Seems like I just get unlucky with these things - but I have an annoying error happening. I have a simple form to enter company information into a table. The below code all works ok with no errors, and finishes off with a "all added" messagebox, no errors at all and there is no "on error resume next" at all (i checked many times). i even put a break in the code to make sure it wasn't being skipped, which it wasb't - but when I go into the table... the data is not entered. I've checked the field settings and all is appropriate data for each field - i even changed a table name in the vba in order to force an error - which it did as expected. i just can't figure it out.

    insSQL = "INSERT INTO Company_tb (CompanyName, CompanyAddress, CompanyCity, CompanyPostCode, CompanyEORI, CompanyCountryID, IsOurCompany, EmailAddre) " & _
          "VALUES ('" & Me.CompanyName & "', '" & Me.CompanyAddress & "', '" & Me.CompanyCity & "', '" & Me.CompanyPostCode & "', " & _
          IIf(IsNull(Me.CompanyEORI), "Null", "'" & Me.CompanyEORI & "'") & ", " & Me.CompanyCountryID.Column(0) & ", " & _
          IIf(Me.IsOurCompany = True, "True", "False") & ", '" & Nz(Me.EmailAdd, Null) & "');"
    Debug.Print insSQL
    db.Execute (insSQL)

and below is the result of a debug.print so i know the vba is running ok;

INSERT INTO Company_tb (CompanyName, CompanyAddress, CompanyCity, CompanyPostCode, CompanyEORI, CompanyCountryID, IsOurCompany, EmailAdd) VALUES ('Company A', 'Address line', 'City name', '1000', Null, 130, False, 'mail@something.com');

Any help would be hugely appreciated.

Thanks!

Hey, I'm setting up a small database in which the stock of a small warehouse is managed. I have three tables, one is the tblArticle in which the basic information of the articles is stored, a tblOrders in which all information is stored when an article is ordered again, currently a new entry is created with each order even for the same article, the table Withdrawal in which the stock withdrawals are stored, even several times for one article. The last table is the tblCategories, in which the articles are categorised into one of the three main categories, which also have their specific subcategories. Now I want to display the total quantity of articles from all tables, i.e. in a qry. When I calculate the information for the individual items I always get the wrong numbers, I think it always forms a Cartesian product, how can I solve this?

Hi Everyone,

Can anyone recommend a brand or type of NAS (preferably with a simple setup) that works well as a backend file server for MS Access? It needs to host the tables and share them with a few users on the same internal network who have their own front ends. Our SBS server at work is being retired soon, so I need to find a replacement. The database is around 200MB, so nothing too demanding.

I recall hearing about something specific to consider when running an Access backend on a NAS—possibly related to file structure, protocols like SMB or NTFS, or Windows file sharing—but I can’t quite remember the details. I’m looking at options like Synology, QNAP, or Terramaster. If anyone has experience with this or knows what makes a NAS particularly good (or bad) for MS Access sharing, I’d really appreciate your advice. Is brand, file structure, CPU, or RAM the most critical factor here?

I could buy a PC instead, but I think a plug-and-play NAS might be better for my needs, especially since I want a second drive for backups and general file sharing. However, if a simple PC setup with SSDs would work better for sharing the Access backend, I’m open to suggestions. The goal is live sharing of the Access backend over a small internal network (max 5 users) and a second drive to take daily copies of the database.

Thanks in advance!

Hi all, can anyone tell me why MilesTravelledTB and MonthsUsedTB are visible on form load, and not responsive to SubcategoryCB updates, whereas the enabled/disabled part of the subroutine seems to be working fine please? Properties are set to not visible and disabled. Code:

Private Sub Form_Load()

' Initially show all subcategories, including CategoryID

Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT ORDER BY SubcategoryName"

Me.SubcategoryCB.ColumnCount = 3 ' Set the column count to 3

Me.SubcategoryCB.Requery

' Hide and disable the relevant text boxes on form load

Me.MilesTravelledTB.Visible = False

Me.MilesTravelledTB.Enabled = False

Me.MonthsUsedTB.Visible = False

Me.MonthsUsedTB.Enabled = False

' Set the initial state of the CategoryManuallySelected flag

CategoryManuallySelected = False

End Sub

Private Sub CategoryCB_AfterUpdate()

' Clear the SubcategoryCB value and filter based on the selected Category

Me.SubcategoryCB.Value = Null

Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName, CategoryID FROM ExpenseSubcategoryT WHERE CategoryID = " & Me.CategoryCB.Value & " ORDER BY SubcategoryName"

Me.SubcategoryCB.Requery

' Update column visibility and clear relevant fields

ClearRelevantFields

UpdateColumnVisibility

' Set the flag to indicate manual selection

CategoryManuallySelected = True

End Sub

Private Sub SubcategoryCB_AfterUpdate()

If Not CategoryManuallySelected Then

' Access the CategoryID directly from the combo box

Dim CategoryID As Integer

CategoryID = Me.SubcategoryCB.Column(2)

' Update the CategoryCB with the corresponding category

Me.CategoryCB.Value = CategoryID

End If

' Update column visibility and clear relevant fields

ClearRelevantFields

UpdateColumnVisibility

End Sub

Private Sub UpdateColumnVisibility()

Select Case Me.SubcategoryCB.Value

Case 16 ' Example Subcategory for Miles Travelled

Me.MilesTravelledTB.Visible = True

Me.MilesTravelledTB.Enabled = True

Me.MonthsUsedTB.Visible = False

Me.MonthsUsedTB.Enabled = False

Me.Amount.Locked = True

Me.Amount.Value = ""

Case 47 ' Example Subcategory for Months Used

Me.MilesTravelledTB.Visible = False

Me.MilesTravelledTB.Enabled = False

Me.MonthsUsedTB.Visible = True

Me.MonthsUsedTB.Enabled = True

Me.Amount.Locked = True

Me.Amount.Value = ""

Case Else

Me.MilesTravelledTB.Visible = False

Me.MilesTravelledTB.Enabled = False

Me.MonthsUsedTB.Visible = False

Me.MonthsUsedTB.Enabled = False

Me.Amount.Locked = False

Me.Amount.Value = ""

End Select

End Sub

Private Sub MilesTravelledTB_AfterUpdate()

If IsNull(Me.MilesTravelledTB.Value) Or Me.MilesTravelledTB.Value = "" Then

Me.Amount.Value = ""

Else

Dim miles As Double

miles = Me.MilesTravelledTB.Value

If miles <= 10000 Then

Me.Amount.Value = miles * 0.45

Else

Me.Amount.Value = (10000 * 0.45) + ((miles - 10000) * 0.25)

End If

End If

End Sub

Private Sub MonthsUsedTB_AfterUpdate()

If IsNull(Me.MonthsUsedTB.Value) Or Me.MonthsUsedTB.Value = "" Then

Me.Amount.Value = ""

Else

Dim months As Integer

months = Me.MonthsUsedTB.Value

Me.Amount.Value = months * 26

End If

End Sub

Private Sub ClearRelevantFields()

Me.MilesTravelledTB.Value = ""

Me.MonthsUsedTB.Value = ""

Me.Amount.Value = ""

End Sub

I am trying to run a simple search query from a form, but every time I want to search something else with the same form I have to go back to the query and open design mode, if I don't the results will not change. I would've looked this up but it's apparently too specific to Google :( thankyou in advance.

I'm using Access version 2409 that was included in Microsoft Office 365 Apps for Business.

In my database I've got a Clients table, with a ClientID field used as a primary index (random number between 1000 and 9999). There's a Client form which allows the user to add/change/delete client records. There's a "Add Client" button that generates a new ClientID, creates a new record and fills in the ClientID. Here's the code that I came up with to do that:

Private Sub cmdNewClient_Click()
    Dim NewClientID As Integer
    Dim AvailableClientIDFound As Boolean
    Const minlimit = 1000   ' Lowest Client ID
    Const maxlimit = 9999   ' Highest Client ID

    AvailableClientIDFound = False
    Do Until AvailableClientIDFound
        NewClientID = Int((maxlimit - minlimit + 1) * Rnd + minlimit)
        If DCount("[ClientID]", "Clients", "[ClientID] = " & NewClientID) = 0 Then AvailableClientIDFound = True
    Loop
    Me![ClientID].SetFocus
    DoCmd.GoToRecord acDataForm, "frmClients", acNewRec
    Me![ClientID] = NewClientID
    Me![EstablishmentName].SetFocus
End Sub

It's pretty straightforward. Keep looping around generating random numbers between 1000 and 9999 and checking to see if there's already a client with that number. If there is then try again, otherwise create a new record in the form and fill in the ClientID that was generated.

This works fine 99% of the time but sometimes it generates a number that is already in use. I can't for the life of me figure out why.

A bit of background: The backend is a MySQL database. There are only two users, but whenever a duplicate ClientID is generated it's when only one user had the database open, so I don't think it's some kind of record locking problem. I don't want to use an AutoNumber to generate the ClientID's, for historical reasons the ClientID's are randomly generated.

Can anyone see anything wrong with my code? Is using DCount() the best way to check if a record exists?

EDIT: What I ended up doing is instead of just looking for an unused random ClientID and then telling the form to go to a new record and filling in the new ClientID, I instead made it actually write a new record to the Clients table using the new ClientID, then requery the form's datasource to pick up the new record, then jump to that record in the form.

So far it seems to be working, only time will tell.

Thanks everyone for your suggestions.

https://preview.redd.it/p2rpyg75ga3e1.png?width=751&format=png&auto=webp&s=7dbf60b9e32d26b70e9f1442cf11cd4022d4ed13

https://preview.redd.it/8uk0n9r5ga3e1.png?width=768&format=png&auto=webp&s=0545be5a41c038ea358f92f712e257dc4d0eb264

Is it possible in my Sales table, to not have to re-type data in the "brand", "model" and "size" fields but have it update itself when entering the primary key (shoe_ID) from my shoe table? This is for a university project where we are given an online store and have to make a small database for it.

Is anyone else seeing an increase in error 2101 in their access front end applications? We've been getting them on lines where we are setting the .columnhidden = false and where we are setting the picture for an image control to a bitmap on the network (logo for a report).

Trying to figure out if it is an Access update issue, something that was ignored before, or a problem with that Monaco SQL Editor. Not sure just yet. Let me know what ya'll are experiencing.

Hello,

I have a large amount of data formatted like so:

I would like to take the data and format it like this

The data set is very large with over 30,000 jobs and 160 Item types. I want it formatted like this because I want to do a linear regression and this seems like the best way to format the data. I am still new to Access and SQL and would like any help y'all can provide.

Thank you

Which should be it? Like, with minimal coding, easy to create report to be printed, and easy to do a query too. I mean, its not like the company im working now is complaining but i know there is a better alternative to ms access.

Our system is running on mysql for the backend db and ms access for the frontend.

I have this error (see image) and cannot for the life of me find the root cause.
It is triggered when a button that close a form is clicked. I have searched all VBA code and events on the form and in button and fields and cannot find anything that would trigger the event that is causing the error.

I have searched all vba for T_LogCompany, all queries, and all tables.

I have also compacted the database recently.

Any ideas how I can find where this might be triggering?

https://preview.redd.it/fvksxv1ury2e1.jpg?width=1794&format=pjpg&auto=webp&s=3807afff9c69c96eb82523c56efea49a75a4f022

Hi all, I previously posted about this project but have changed my approach based on the very helpful comments I received. I'm now nearly there:

I have a Subfom within a Form which contains 2 combo boxes which draw on two related tables;
- ExpensCategoryT (1) which contains the fields CategoryID, CategoryName, TypeID (ExpenseTypeT is another table not relevant at them moment).
- ExpenseSubcategoryT (many) which contains the fields SubcategoryID, SubcategoryName, and CategoryID.

The desired behaviour is as follows:

Form_Load()
Display all Categories
Display all Subcategories
Flag set to Not CategoryManuallySelected

CategoryCB_AfterUpdate()
Filter SubcategoryCB by CategoryID
Set flag to CategoryManuallySelected

SubcategoryCB_AfterUpdate()
If Not CategoryManuallySelected Then
Automatically select CategoryID in CategoryCB to corresponds to the selected SubcategoryID
Display all Categories (allowing the user to manually select an alternative Category if they wish which would the re-filter SubcategoryCB as above)
Flag stays set as Not CategoryManuallySelected
BUT
If CategoryManuallySelected Then
Make no change to CategoryCB state (user should continue to be able to manually select an alternative Category if they wish which would the re-filter SubcategoryCB as above)
Flag stays as CategoryManuallySelected

I.e. at first, the user should be able to select subcategories which should retrieve related categories, but the user should always be free to select an alternative category and if at any point they do, then the behaviour should be limited to category selection filtering subcategory choices from then on.

The following code has almost achieved this but it does not allow for a change of behaviour, i.e. If a Subcategory is selected at any time, the corresponding Category is retrieved, but it cannot then be manually changed to re-filter the subcategory combo. I've tried various AIs but to no avail. Can anyone help?!

Private Sub Form_Load()

' Initially show all subcategories

Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName FROM ExpenseSubcategoryT ORDER BY SubcategoryName"

' Set a flag to indicate that the CategoryCB has not been manually selected

CategoryManuallySelected = False

End Sub

Private Sub CategoryCB_AfterUpdate()

' Filter subcategories based on the selected Category

Me.SubcategoryCB.RowSource = "SELECT SubcategoryID, SubcategoryName FROM ExpenseSubcategoryT WHERE CategoryID = " & Me.CategoryCB.Value & " ORDER BY SubcategoryName"

Me.SubcategoryCB.Requery

' Set a flag to indicate that the CategoryCB has been manually selected

CategoryManuallySelected = True

End Sub

Private Sub SubcategoryCB_AfterUpdate()

If Not CategoryManuallySelected Then

Dim CategoryID As Integer

CategoryID = DLookup("CategoryID", "ExpenseSubcategoryT", "SubcategoryID = " & Me.SubcategoryCB.Value)

Me.CategoryCB.RowSource = "SELECT CategoryID, CategoryName FROM ExpenseCategoryT WHERE CategoryID = " & CategoryID & " ORDER BY CategoryName"

Me.CategoryCB.Requery

Me.CategoryCB.Value = CategoryID

End If

End Sub

Sorry, I'm very new to MS Access so I will be very descriptive of my issue.

I created a FORM that displays customer's basic info (getting its data from CUSTOMER TABLE) and have a COMBOBOX that has an ON CHANGE event like below. The dropbox with "search as I type" functions work but when i select the "CompanyName" from the dropdown list, it does not update the form to display the information in the table.

How can i solve this issue?

Private Sub CustomerCombo_Change()

CustomerCombo.RowSource = "SELECT CustomerID, CompanyName " & _

"FROM CustomerQ " & _

"WHERE CompanyName Like ""*" & CustomerCombo.Text & "*"" " & _

"ORDER BY CompanyName;"

CustomerCombo.Dropdown

End Sub

I have a complex report that works in the front end of Access, but once I connect to the back end (SharePoint Lists), the report no longer works - once I input 3 parameters, the report comes up blank. I've checked relationships (the report is based on a multiple table query) and all other reports are working perfectly. It works perfectly when not connected to SharePoint backend.

I am a student in college right now and am struggling trying to accomplish certain tasks in access that I need to do for a proposal. is there any chance one of you database experts would be willing to help me with a few things for my project? preferably on a discord call or something.

Hi, is it theoretically possible to create a form with 3 combo boxes all of which filter each other (or set values in the case of 1 to many relationships) rather than just having cascading updates one way only? TIA!
Clarification:
Box 1 must filter boxes 2 and 3,
Box 2 must filter boxes 1 and 3.
Box 3 must filter boxes 1 and 2.
UPDATE: I've given up on this, it was ill-conceived and overly complicated. I've gone for a simpler solution. Thanks to everyone who helped.
UPDATE on my UPDATE: Here's a post on how I'm getting on with my revised solution if anyone's interested - still wildly out of my depth! https://www.reddit.com/r/MSAccess/comments/1gyqb1d/how_to_trigger_a_change_in_related_combo_box/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

I have simple form and simple table inside. These four events with simple MsgBox for tests. Cannot get these events working. File is in trusted location, all the protections turned off, VBA and ActiveX allowed. Code is written thru the form properties to avoid mistakes.

Googling, copiloting, nothing helped.

Edit1: tried to add Enter event to table in form - Table1_Enter() - and that one is working. Insert and Update not.

Hi

https://preview.redd.it/vf5nyp5iw02e1.png?width=489&format=png&auto=webp&s=b8ecfd85b5c606258527ecebdad652eb01d3b233

I am fairly new to MS Access, but I trying to import a data sheet with a date and timestamp into MS Access from Excel.
If I choose Date with time data type the data wont appear in my table when imported. It only works if I choose short text. But by doing that none of the date functions work. I would like to be able to import the file as is without having to remove any data before importing. Can you help?

https://preview.redd.it/v9moml12x02e1.png?width=398&format=png&auto=webp&s=ae0aa4b350e709115e4ebce856732ff06cffa50f

https://preview.redd.it/q3fqdx25x02e1.png?width=391&format=png&auto=webp&s=4858a4f75ddf889a299d3484a6ff080d386b9fe0

Again I am really new to this program, so please any suggestion would need to be really specific.

Hi, I have a structure of tblCase to tblContravention which is a many to many relationship. I have created a junction table called tblCaseContravention.

tblCase

CaseID

CaseName

tblContravention

ContraventionID

ActName

Clause

ClauseDescription

So I have created a Junction table with both primary keys from tblCase and tblContravention in tblCaseContravention.

This all works fine typically when I have one combo box selecting the Contravention. But I am trying to create a solution where I have a subform on frmCase, where I select the ActName from one combo box, then a cascading (after update code) Clause in the second combo box, then the ClauseDescription is displayed relating to the ActName and Clause selected. I suppose I may have to create a commit button on a continuous form, that commits the INSERT of the CaseID and ContraventionID to the junction table.

That is the goal, has anyone done anything like this and do you have any advice?

I have an unbound form named Frm_CustomerCard.

On it is a combo box control named Sel_CustPlatID

The Row Source for this control is:

SELECT tbl_CustPlatform.Cust_Platform_ID, tbl_CustPlatform.Platform_Screenname, tbl_CustPlatform.Website_Customer_ID, tbl_CustPlatform.Platform_ID
FROM tbl_CustPlatform
WHERE (((tbl_CustPlatform.Platform_ID)=[Forms]![Frm_CustomerCard]![SelSalesPlat]))
ORDER BY tbl_CustPlatform.Platform_Screenname;

The bound column is 1 tbl_CustPlatform.Cust_Platform_ID.

I have a subform named "Sub_AddNewCustCat"

I am attempting to set the recordsource of this subform to:

tbl_CustCat

where the feild tbl_CustCat.Cust_ID matches the value of tbl_CustPlatform.Cust_ID for the tbl_CustPlatform record identified by the bound value of Sel_CustPlatID

Here is the code I'm attempting to use:

Dim SQL As String
Dim CustLook As Integer

CustLook = DLookup([Cust_ID], [tbl_CustPlatform], [Cust_Platform_ID] = Me.Sel_CustPlatID.Value)



SQL = "SELECT * " _
& "FROM tbl_CustCat " _
& "WHERE tbl_CustCat.[Cust_ID] = " & CustLook & " ; "


Me.Sub_AddNewCustCat.Form.RecordSource = SQL
Me.Sub_AddNewCustCat.Form.Requery

When I attempt to execute the code I'm getting a runtime error: 2465

MS Access can't find the field '|1' referred to in your expression.

The debugger is highlighting this as the problem:

CustLook = DLookup([Cust_ID], [tbl_CustPlatform], [Cust_Platform_ID] = Me.Sel_CustPlatID.Value)

I have no idea what's wrong in that statement.

I'm building a database to track design changes to a product.

Each design change comes with affected serial numbers. This can be several ranges of, or single numbers, then usually (but not always) all subsequent. For example "340-348, 352-364, 366, 368 and subsequent", or just "356", or "all".

I need to store all this in a searchable way so that I can create a list of all design changes affecting a specific serial number.

For context I have last used Access ~10 years ago in high school so I'm a bit out of my depth, but this would replace manually going trough a very old and janky excel sheet with 1000+ design changes every time, so it's worth a lot of effort.