New Form with Current Records (1 Viewer)

[shafara7]

I have searched about this and founs many familiar thread but it does not help with my forms.
So I have a form named frmTeilebeschaffung and it has a subform which displays some records and a button which will open a new form named frmDatumGueltigkeit.

What I want to do with my form is that when I click on a records on frmTeilebeschaffung and clicked on the button, the new form frmDatumGueltigkeit will open with the current record.
But it did not open the current record. It only displays the first ever record on the table.
See photo.

The VBA code on the button is

Private Sub btnTeilVorOrt_Click()
     DoCmd.OpenForm "frmDatumGueltigkeit", , , , , , "[txtMANr]='" & Me![txtMessauftragNr] & "'"
End Sub

The VBA code for the new form frmDatumGueltigkeit is

Private Sub Form_Load()
    Me.RecordSource = "SELECT tblMessauftrag.* FROM tblMessauftrag ;"
End Sub

Btw txtMANr is the name for the text box Messauftrag Nr on frmDatumGueltigkeit and txtMessauftragNr is the name for text box Messauftrag Nummer on frmTeilebeschaffung.
Did I miss any codes or link?

 

[Gasman]

I hace way less commas in a call to a form where I do the same thing?

DoCmd.OpenForm "frmClient", , , "ClientID = " & Me![lstClient].Column(0)

 

[shafara7]

Tried that too but still the first record appeared. not the current record.
What does Column(0) means?

 

[Minty]

By setting the record source on load you are overwriting the filter you set in the form open method from the other form.

 

[shafara7]

Okay so I have removed the Form_Load record source but when I clicked on the button, the parameter value input window appear asking for the value txtMessauftrag. And when I entered the value for example 2017-0249, the new forms open with no data record on it.

 

[Minty]

Try using Me.txtMessauftrag and check the spelling of the control name.

 

[shafara7]

Me.txtMessauftrag will also generate error msg.

Wait I am confused. Do I have to use the name of the field from the table or the name of the text box on the form?

For example,
TableA
IDfieldtable field1 field2 field3 ...
2017-0200 ..
2017-0201 ..
2017-0202 ..

FormTeilebeschaffung with subform..
IDfieldA * field1 field2 field3 ... *field name is IDfieldA but control source name is IDfieldtable
2017-0200 ..
2017-0201 ..
2017-0202 ..

FormDatumGueltigkeit
IDfield: ___ <- textbox name is IDfieldB with control source IDfieldtable
field1: ___
field2: ___

So what exactly should I write on my codes?
DoCmd.OpenForm "frmDatumGueltigkeit", , , , , , "[IDfieldtable]='" & Me![IDfieldtable] & "'"
Or
DoCmd.OpenForm "frmDatumGueltigkeit", , , , , , "[IDfieldA]='" & Me![IDfieldB] & "'"

I hope you can understand.

 

[Minty]

I would always use the control name as that is what the Me. property is referring to.
I tend to rename textbox controls on forms to txtYourFieldName to make sure I know I am referring to the control.
same with cmb Combobox lst Listboxes etc etc

 

[shafara7]

Okay since both of the form shares the same field on the table, I would use lngMessauftragNr because it is my control source for both forms, right?

Either way, the second form still does not open with the current selected record.
Should I put SetFocus somewhere?
Because I think when I click the button, it did not know which record is selected, hence it shows the first record.

 

[Gasman]

Tried that too but still the first record appeared. not the current record.
What does Column(0) means?

That was just where I was getting my value for the record from. In this case it was from a listbox.

 

[shafara7]

Hello, I still couldn't fix my form. I have made a dummy copy of my form.
Can you have a look what I did wrong?
I want the form CustomerDetails to show the details of the customer that is selected. But When I clicked the button, nothing appears.

 

[arnelgp]

Heres the fix. Added a module also.

 

[shafara7]

Thank you arnelgp. I clicked the button but a run-time error occured.
Run-time error '2467':
The expression you entered refers to an object that is closed or doesn't exist.
When I clicked on Debug, it highlighted the following codes..
Forms!frmCustomerDetails.RecordSource = _
"SELECT * FROM tblCustomer " & _
"WHERE tblCustomer.txtCustomerID = " & Chr(34) & Me!sfmOrder.Form.txtCustomerID & Chr(34)

 

[arnelgp]

change this:

"WHERE tblCustomer.txtCustomerID = " & Chr(34) & Me!sfmOrder.Form.txtCustomerID & Chr(34)

TO:

"WHERE tblCustomer.txtCustomerID = " & Chr(34) & Me!sfmOrder.txtCustomerID & Chr(34)

 

[arnelgp]

Sorry again, we have to use the bang (!)

"WHERE tblCustomer.txtCustomerID = " & Chr(34) & Me!sfmOrder!txtCustomerID & Chr(34)

 

[shafara7]

It worked on the dummy records but not on my actual records
The parameter value window poped-up and when I entered the value, the Form does not open at all.

 

[arnelgp]

it will not run on Parameter Query. create new query (without parameter) and use that query as your recordsource.
better yet upload a trimmed down version of your db.

 

[shafara7]

I have tried to make a trimmed version of my db but my db has so many different relationship with other forms, it just ruined everything if I try to remove even one form, and the size is bigger than 2MB even after deleteing unimportant stuff.

So I decided maybe I shouldn't create a new form, because all I wanted is to do the validation rule on my subform, but it is not working because my subform is uneditable.

Can you take a look at the screenshots below.
In Photo 1), the 3 Fields are coloured with green and red but when I checked the Format conditions Photo 2), they are only grey.
I couldn't find any codes that is related to this colour.
The fields are set to Locked and Not active.
But when I set it to Unlocked and Active, I can edit the dates but the colour for the field is no longer there.

Another problem that I couldn't figure out is at Photo 3) and Photo 4).
As you can see the background colour of the records are blue according to the Messaufgabe combo box on the left.
But I couldn't find any codes or settings that make it appear like this. There is no background format conditions underneath it.

 

[shafara7]

Okay I'm sorry I think I know why it shows all grey. I just figure out there is an "Activate" button at the Format Conditions window. It is grey because it is inactive. When I clicked the button, the colour will be visible.