Code fails when adding new record

[csolomon]

Hello:

I have a form that allows me to add new records. In the onCurrent event, I have:

Private Sub Form_Current()
'Requery Job Number
Me.cbojobNumber.Requery
'Requery DM_Mix Combo
Me.DM_Mix_cbo.Requery
'Requery SF_MixBatch.DM_materialNo
Me!SF_MixBatch.Form!DM_MaterialNo.Requery
'Requery Observation list
Me.List71.Requery
 
>>>>'Open MixDesign in the background where DM_Mix = DM_Mix_cbo
 
>>DoCmd.OpenForm "F_MixDesign", View:=acNormal, _
>>WhereCondition:="DM_Mix = " & Forms![F_Batching].DM_Mix_cbo, _
>>WindowMode:=acHidden
End Sub
 

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?

[ChipR]

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.

[DonRayner]

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.

DoCmd.OpenForm "F_MixDesign",acNormal, "TABLE.FIELD = " & Forms![F_Batching].DM_Mix_cbo, acHidden

[csolomon]

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:

DoCmd.OpenForm "F_MixDesign",acNormal, "TABLE.FIELD = " & Forms![F_Batching
].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)

DoCmd.OpenForm "F_MixDesign", _
View:=acNormal, _
WhereCondition:="MixDesign.DM_Mix = " & Forms![F_Batching].DM_Mix_cbo, _
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 '

[ChipR]

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]

[csolomon]

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:

Private Sub btnClose_Click()
DoCmd.Close
If CurrentProject.AllForms("F_MixDesign").IsLoaded Then
DoCmd.Close
End If
End Sub
 

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?

[ChipR]

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.

'begin CODE
    Dim db As Object
    Dim records As Object
 
    Set db = CurrentDb
    Set records = db.OpenRecordset(str)
 
    'do stuff
 
    records.Close
    Set records = Nothing
    db.Close
    Set db = Nothing
'end CODE
 

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?)

[DonRayner]

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

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.

[DonRayner]

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.

[csolomon]

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'.

[ChipR]

& [Forms]![F_Batching]![DM_Mix_cbo] & """,
should be
& [Forms]![F_Batching]![DM_Mix_cbo] & """",

[csolomon]

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

[ChipR]

Then it would just be
DoCmd.OpenForm "F_MixDesign", acNormal, , "[MixDesign].[DM_Mix] = " _
& [Forms]![F_Batching]![DM_Mix_cbo], acHidden

[DonRayner]

@csolomon
Don't need the """" if it's an integer. We were also missing an extra , before acHidden.

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

[csolomon]

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.

[csolomon]

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?

[ChipR]

You can't open the form on the new record because it hasn't been created yet.

[csolomon]

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.

[ChipR]

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.

[csolomon]

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?

[ChipR]

Depends, what do you want to open the other form to?

[csolomon]

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.

[ChipR]

Then just open it to the first record.

If IsNull(DM_Mix_cbo) OR DM_Mix_cbo= "" then
  strWhere = ""
Else
  strWhere = "[MixDesign].[DM_Mix] = " & DM_Mix_cbo
End If
 
DoCmd.OpenForm "F_MixDesign", acNormal, , strWhere, , acHidden

[csolomon]

Chip you are a genius!!!

That worked!