Search results

Minty, in my case it feels like Access does know about the table being Not Null and it tries to validate the form field contents too early. The error I got pops up before any Event trigger I tried, and it occurs if the linked table has that field set to Not Null but does not occur if the field...

I'm going to mark this as solved. I don't know why Access behaves this way, and I think it is a design error or bug in Access itself, but by disabling the Not Null validation at the server level I can use form event code to ensure data is entered. While I'd like to know why this behavior...

Ok, an update. I changed that Last Name col in the SQL Server DB to allow nulls and relinked the table. This (to me, odd) behavior disappeared. I can use a workaround where I rely solely on event code in the Access front end to prevent null/empty values. But, it is still baffling to me why the...

I can try, but it uses SQL Server as a backend. Right now it's a server running SQL Server Express on a PC at home. It can also be linked to an Azure server, but that has PI like phone nums and addresses and it'd be hard to anonymize it. I don't think anything would work since you can't get to...

Thanks, Pat! It doesn't seem to me that any specific line of code is failing. My uneducated guess is that it is something inherent in what Access does before any of the VBA code is triggered. But I don't know how to detect that. I added code per your model, example in the Cancel button When...

Pat, I did see your comment in #3. Maybe I don't understand it. If I understood it you meant that the way to try to delete text in a text box is to use Esc? I tried selecting the field text, the Del key and then Esc. That restored the original value. But if a user somehow deleted the prior entry...

Thanks for all this so quickly! I just tried the Before Update event on the field that has been deleted. It fired but after the error dialog in my image opened. I wasn't sure which VB code to attach as none of it seemed to be invoked before the error appeared, but it is below. This is all the...

I have a form created to allow editing of a record in a table of people. The db is in SQL Server. Most of the columns are Not Nullable, e.g., Last Name, First Name. This form (mostly) works when editing a record, that is, if I change a value such as name and save it. I am adding event code to...

I uploaded that version and a little later stumbled on the DoEvents solution. Here is that same db with the newer function code.

Yes, the caption shows and gets updated by the DoEvents function.

SOLVED! I just poked around using Google about refreshing forms and discovered the DoEvents VBA function. Using that right after the statements that set the control's caption causes it to appear. Not knowing how access handles processing while a form is open and it is doing background VBA tasks...

Here's a sanitized copy but you can see the behavior. Table links have been removed. The connect string in the UnpauseSQL function has had the user and password changed to incorrect values, so the connect action always fails. I also added code to it to try to force the caption of the label...

Some progress. I removed all event procedures from the form that is used to show that action is occurring (named "Loading Toast"). I am using this AutoExec macro (GetUser() is a simple function that gets the user, domain and machine names and stores them in a global variable): Because the...

Keep in mind this is for animal shelters, so their cost has to be minimal or ideally free. The Azure SQL license types confuse me, so I'm not sure what each nonprofit would really have to pay to get their own database. This use case is a small database, so the data storage should be minimal...

Status is declared as Boolean. I have looked at it in VBA debugger and it does return a value of True (it would only ever return False if it could not connect to the DB in 15 tries).

I have an Access database that connects to an Azure SQL Server db, and all tables are in SQL server. I'm using a free trial tier, which pauses the SQL database after an hour. If I load a form when Access opens the accdb file that shows a combo box based on querying from a small (like 10 row)...

Moderate level of Access skills. Near Philadelphia PA USA. Looking to create reusable apps connected to Azure SQL Server that support several animal shelter business processes that they can implement cost free.