By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Code fails when adding new record

100+
P: 166
Hello:

I have a form that allows me to add new records. In the onCurrent event, I have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. 'Requery Job Number
  3. Me.cbojobNumber.Requery
  4. 'Requery DM_Mix Combo
  5. Me.DM_Mix_cbo.Requery
  6. 'Requery SF_MixBatch.DM_materialNo
  7. Me!SF_MixBatch.Form!DM_MaterialNo.Requery
  8. 'Requery Observation list
  9. Me.List71.Requery
  10.  
  11. >>>>'Open MixDesign in the background where DM_Mix = DM_Mix_cbo
  12.  
  13. >>DoCmd.OpenForm "F_MixDesign", View:=acNormal, _
  14. >>WhereCondition:="DM_Mix = " & Forms![F_Batching].DM_Mix_cbo, _
  15. >>WindowMode:=acHidden
  16. End Sub
  17.  
the '>>' is where i think the issue is. Initially when the form opens, it shows old records (the DM_Mixes match up), but I would like to add a new record. I have a button that adds new records to the form. I think the issue is that when I want to add a new record, i want that record to co-inside with the record in the F_MixDesign form. I think it is because of this piece:
>>WhereCondition:="DM_Mix = " & Forms![F_Batching].DM_Mix_cbo, _
Until the new record is created on the F_Batching form, there would be no matching record in [F_MixDesign].DM_Mix...Is there a way that I could do a check to first see if there is a matching DM_Mix in both forms, if there is, then create a new record on the form?
Feb 3 '09 #1
Share this Question
Share on Google+
23 Replies


Expert 100+
P: 1,287
I'm not clear on what you're doing. It sounds like:
1. You want information from a form that isn't open to add to the new record.
2. You want to open the form to a record that doesn't exist yet.

Why don't you write a function in your main form that does a DLookUp or whatever calculation to get the information you need to add to a new record? There is no reason to open the other form in the first place.
Feb 3 '09 #2

DonRayner
Expert 100+
P: 489
Refer to the Table/Query field that DM_Mix is based on rather than the control on the form.

You are also missing a comma between your view and where conditions. You have the where condition set in place for the filter condition.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "F_MixDesign",acNormal, "TABLE.FIELD = " & Forms![F_Batching].DM_Mix_cbo, acHidden
Feb 4 '09 #3

100+
P: 166
What I want to do is get a calculated field from my form to another form. To do this, I open the invisible form on the record that is current in the form I am using.

Chip, I would just use the controls in the calculation and transfer them to the new form, however the same controls do not exist on the new form, so I think I would receive an error bc access would not be able to find the controls used in the calculation. I thought it would be easier to have the form (F_MixDesign) open as invisible. the issue that i found is that just beecause I have closed the form (F_batching) does not mean that the invisible form has closed. I am trying to find a solution to this issue.

DonRayner,
I tried your approach:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "F_MixDesign",acNormal, "TABLE.FIELD = " & Forms![F_Batching
  2. ].DM_Mix_cbo, acHidden
Here is mine (I never saw where the comma you said was missing should be, yours looks like mine to me)
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "F_MixDesign", _
  2. View:=acNormal, _
  3. WhereCondition:="MixDesign.DM_Mix = " & Forms![F_Batching].DM_Mix_cbo, _
  4. WindowMode:=acHidden
I have a button that is supposed to allow me to add a new record to the F_Batching form however, when it is pressed, I get this error:

Syntax Error (Missing operator) in query expression 'DM_Mix '
Feb 4 '09 #4

Expert 100+
P: 1,287
If you wanted to make sure to close the hidden form when the visible form was closed, put code to check if the form is open, and if so close it, in the Form_Unload event.

What I was trying to say is, when you open the hidden form, the control on that form calculates its value. Whatever the calculation is, you can do it in a function; you don't need to open the form just to get default inputs to the calculation. If the calculation involves other fields on the form, you can get this information in the function also, they are calculated the same way when the form opens. You have all the information you need without opening the other form, you could just open a recordset instead.

Is DM_Mix an integer?

The syntax is
DoCmd.OpenForm FormName, [View], [FilterName], [WhereCondition], [etc]
Feb 4 '09 #5

100+
P: 166
How would I go about opening a recordset instead? Is this done in VBA? The thing is that most people might click the 'X' in the top right corner of the form as opposed to clicking the close button on the form, which is where I have this code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnClose_Click()
  2. DoCmd.Close
  3. If CurrentProject.AllForms("F_MixDesign").IsLoaded Then
  4. DoCmd.Close
  5. End If
  6. End Sub
  7.  
which checks to see if the form is loaded and closes it if so. If the user clicked close each time, i wouldn't worry about the open invisible forms, but I guess I have to make it fool proof, so maybe doing what you suggested would be best?
Feb 4 '09 #6

Expert 100+
P: 1,287
Form_Unload should get called in any case. Put a msgbox in that event and see when it gets called depending on how you close the form/application to make sure.

Opening a recordset is pretty basic. For some reason though, I'm not seeing the Code button when I'm replying here at the moment.
Expand|Select|Wrap|Line Numbers
  1. 'begin CODE
  2.     Dim db As Object
  3.     Dim records As Object
  4.  
  5.     Set db = CurrentDb
  6.     Set records = db.OpenRecordset(str)
  7.  
  8.     'do stuff
  9.  
  10.     records.Close
  11.     Set records = Nothing
  12.     db.Close
  13.     Set db = Nothing
  14. 'end CODE
  15.  
Here str can be lots of things. You can put in a table name, and open the whole table, or a query name, or a "SELECT * FROM..." statement. It automatically puts the first record into the records variable. You can access info in the records with
records!FieldName
and that will give you the value in the table/query for that field.
You can advance through records with records.MoveNext to find a specific record, until records.EOF (end of file?)
Feb 4 '09 #7

DonRayner
Expert 100+
P: 489
Here is the syntax for OpenForm

DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

FormName = Form to open (as string)

View = acDesign, acFormDS, acFormPivotChart, acFormPivotTable, acNormal (default), acPreview

FilterName = name of a query in the database (as string)

WhereCondition = A valid SQL statement (Without Where)(as string). This is applied to the forms Filter Properties.

Data Mode = acFormAdd, acFormEdit, acFormPropertySettings (default), acFormReadOnly

Window Mode = acDialog, acHidden, acIcon, acWindowNormal (default),

Opening Arguments = Sets the form's OpenArgs property (as string)

Using that syntax your statement should be
Expand|Select|Wrap|Line Numbers
  1. DoCmd.Openform "F_MixDesign",acNormal,, "[TableName].[FieldName] = " & """" & [Forms]![F_Batching]![DM_Mix_cbo] & """, acHidden
Notice the two commas between acNormal and TableName. Change TableName to the name of the forms recordset source table or query. Change FieldName to the name of the Field in the table that you want to filter on.
Feb 5 '09 #8

DonRayner
Expert 100+
P: 489
How would I go about opening a recordset instead? Is this done in VBA? The thing is that most people might click the 'X' in the top right corner of the form as opposed to clicking the close button on the form, which is where I have this code:
If you set your forms "Close Button" property to No then you wouldn't have to worry about users not using your button to close the form.
Feb 5 '09 #9

100+
P: 166
HI DonRayner,

I tried this:
DoCmd.OpenForm "F_MixDesign", acNormal, , "[MixDesign].[DM_Mix] = " & """" & [Forms]![F_Batching]![DM_Mix_cbo] & """, acHidden"

and I get an error that says, syntax error (comma) in query expression '[MixDesig].[DM_Mix]= "4", acHidden'.
Feb 5 '09 #10

Expert 100+
P: 1,287
& [Forms]![F_Batching]![DM_Mix_cbo] & """,
should be
& [Forms]![F_Batching]![DM_Mix_cbo] & """",
Feb 5 '09 #11

100+
P: 166
I get an error message that says data type mismatch. DM_Mix in both cases is an integer


DoCmd.OpenForm "F_MixDesign", acNormal, , "[MixDesign].[DM_Mix] = " & """" _
& [Forms]![F_Batching]![DM_Mix_cbo] & """", acHidden
Feb 5 '09 #12

Expert 100+
P: 1,287
Then it would just be
DoCmd.OpenForm "F_MixDesign", acNormal, , "[MixDesign].[DM_Mix] = " _
& [Forms]![F_Batching]![DM_Mix_cbo], acHidden
Feb 5 '09 #13

DonRayner
Expert 100+
P: 489
@csolomon
Don't need the """" if it's an integer. We were also missing an extra , before acHidden.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "F_MixDesign", acNormal,,"[MixDesign].[DM_Mix] = [Forms]![F_Batching]![DM_Mix_cbo]",,acHidden
Feb 5 '09 #14

100+
P: 166
Thanks Chip. It appears to be working. Initially I had soemthing similar to that, but for some reason it wasn't working properly. I had this at first:

DoCmd.OpenForm "F_MixDesign", _
View:=acNormal, _
WhereCondition:="MixDesign.DM_Mix = " & Forms![F_Batching].DM_Mix_cbo, _
WindowMode:=acHidden

As you can see, there is no filter section and I didn't know how to do it my way (with the names of the arguments) and have no filter. I just feel that way is more readable.
Feb 5 '09 #15

100+
P: 166
Well Chip,

I still have an issue with this. the code works fine when I am viewing old records, but if I try to add a new one, I get a message on the where condition.

I was trying to explain before that I believe this is because when the form (F_Batching) opens, it opens the record in the F_MixDesign form with a matching ID in the background. In the OnCurrent event of the F_Batching sheet I have this:

'Open MixDesign in the background where DM_Mix = DM_Mix_cbo

DoCmd.OpenForm "F_MixDesign", acNormal, , "[MixDesign].[DM_Mix] = " _
& [Forms]![F_Batching]![DM_Mix_cbo], acHidden

I believe that when I go to a new record there should be a matching ID for it in MixDesign, because it was first created in on this form, and then the DM_Mix is used on the Batching form. How do I tell it to open the MixDesign form on the new record?
Feb 5 '09 #16

Expert 100+
P: 1,287
You can't open the form on the new record because it hasn't been created yet.
Feb 5 '09 #17

100+
P: 166
The record gets created on the F_MixDesign form. I use the records I create there to update the batching sheet. IDK what to do. I have in the onCurrent this:
'Open MixDesign in the background where DM_Mix = DM_Mix_cbo

DoCmd.OpenForm "F_MixDesign", acNormal, , "[MixDesign].[DM_Mix] = " _
& [Forms]![F_Batching]![DM_Mix_cbo], acHidden

which is what i think is prohibiting me from opening a new record. Maybe I could have that in the onLoad event? I have to have the form open as invisible so that I can use the controls on the page.
Feb 5 '09 #18

Expert 100+
P: 1,287
So what is the value of DM_Mix_cbo when this code is run? If it's when you move to a new record, it may be empty. Then your where condition in the OpenForm is MixDesign.DM_Mix = "", which probably shouldn't match anything. Maybe you want to set DM_Mix_cbo equal to something, then open the form.
Feb 5 '09 #19

100+
P: 166
When the code is ran, the value of DM_Mix_cbo is 4. That is the value of the first record in the form. When I try to add a new record, the DM_Mix_cbo is Null (I just saw that). What would I make it equal to?
Feb 5 '09 #20

Expert 100+
P: 1,287
Depends, what do you want to open the other form to?
Feb 5 '09 #21

100+
P: 166
Well as I said, the only reason the F_MixDesign is open at all is to copy the value in a control to the F_Batching form, where the DM_Mixes are equal.

It really doesn't matter what the form opens to, I just need to be able to add new records.
Feb 5 '09 #22

Expert 100+
P: 1,287
Then just open it to the first record.
Expand|Select|Wrap|Line Numbers
  1. If IsNull(DM_Mix_cbo) OR DM_Mix_cbo= "" then
  2.   strWhere = ""
  3. Else
  4.   strWhere = "[MixDesign].[DM_Mix] = " & DM_Mix_cbo
  5. End If
  6.  
  7. DoCmd.OpenForm "F_MixDesign", acNormal, , strWhere, , acHidden
Feb 5 '09 #23

100+
P: 166
Chip you are a genius!!!

That worked!
Feb 5 '09 #24

Post your reply

Sign in to post your reply or Sign up for a free account.