Okay, so here's a monument to my naivety with SQL.
What I'm trying to do is a little complicated, but I'll try my best to explain.
Upon filling in an ID number in a form, I want two pieces of data to automatically update: The Part Name associated with given ID, and the quantity remaining for the ID.
The only way I know how to do this is, while the main form is open (let's call it form A), open a different form (let's call it Form B) that displays the data, set the value on Form A, and close form B. (I tried just doing the set value by itself, but the form displaying the data I want the value set to needs to be open.)
I have some code in the "On Lost Focus" event of the ID field so that after the ID field is updated, the Part name and Quantity remaining for that ID are updated. If the ID doesn't exist in the INBOUNDS table, a message box pops up stating that, and if the ID field is left blank, it doesn't do anything.
If the ID field IS filled in with a valid ID, what I want it to do is open up 2 forms, one that figures out the Part name of the ID in the ID field, and one that calculates the total quantity remaining.
Here is what I have.
Ok, before anyone comments, I DO know that it's bad form to have spaces in names... I'm working on it. For now, though, this is what I've got, and I'm very careful to bracket anything with spaces.
Ok, moving on. For whatever reason, this is either giving me an error and/or shutting down Access. I HAVE narrowed it down to this code. In fact, if I take out the code in bold purple, it works just fine.
So, two questions.
First of all, is there a better way to do this?
And secondly, if not, why is my code crashing Access?
UPDATE:
I just realized I didn't put up the error message, but I also just realized something else, too.
I get this error message:
But the weird thing is, it still updates the Quantity Remaining field on the main form, as if the LEFTbyIDout form had been opened with no problem. The Continue button is grayed out, but the End, Debug, and Help buttons are clickable.
What I'm trying to do is a little complicated, but I'll try my best to explain.
Upon filling in an ID number in a form, I want two pieces of data to automatically update: The Part Name associated with given ID, and the quantity remaining for the ID.
The only way I know how to do this is, while the main form is open (let's call it form A), open a different form (let's call it Form B) that displays the data, set the value on Form A, and close form B. (I tried just doing the set value by itself, but the form displaying the data I want the value set to needs to be open.)
I have some code in the "On Lost Focus" event of the ID field so that after the ID field is updated, the Part name and Quantity remaining for that ID are updated. If the ID doesn't exist in the INBOUNDS table, a message box pops up stating that, and if the ID field is left blank, it doesn't do anything.
If the ID field IS filled in with a valid ID, what I want it to do is open up 2 forms, one that figures out the Part name of the ID in the ID field, and one that calculates the total quantity remaining.
Here is what I have.
Code:
Private Sub ID_LostFocus()
If IsNull([ID]) Then
Exit Sub
End If
If Not IsNull([ID]) Then
If DCount("*", "INBOUNDS", "[ID]=Forms![OUTBOUND NEW]![ID]") < 1 Then
MsgBox "There is no record for this ID!"
Exit Sub
Else
DoCmd.OpenForm "OUTBOUNDS ID 2 PART", , , , , acHidden
[Forms]![OUTBOUND NEW]![PART] = [Forms]![OUTBOUNDS ID 2 PART]![PART]
DoCmd.Close acForm, "OUTBOUNDS ID 2 PART", acSaveNo
[COLOR=Purple][B]DoCmd.OpenForm "LEFTbyIDout", , , , , acHidden
[Forms]![OUTBOUND NEW]![QTY REM] = [Forms]![LEFTbyIDout]![QTY]
DoCmd.Close acForm, "LEFTbyIDout", acSaveNo[/B][/COLOR]
End If
End If
End SubOk, moving on. For whatever reason, this is either giving me an error and/or shutting down Access. I HAVE narrowed it down to this code. In fact, if I take out the code in bold purple, it works just fine.
So, two questions.
First of all, is there a better way to do this?
And secondly, if not, why is my code crashing Access?
UPDATE:
I just realized I didn't put up the error message, but I also just realized something else, too.
I get this error message:
But the weird thing is, it still updates the Quantity Remaining field on the main form, as if the LEFTbyIDout form had been opened with no problem. The Continue button is grayed out, but the End, Debug, and Help buttons are clickable.
Last edited:
