Bound form Issue

[pbaldy]

No worries, when you have time can help me to take a look on this.

My point was that while you added the line to set the variable, you took out

Me.Dirty = False

so the record is not going to save automatically. In a brief test the code worked when I added that back. I did have to fix the first record, which had a sequence of 0.

[orange]

Based on your comments, attached is a rough overview of the process(es) involved.

How much of this will be supported by your database? Violations, Objectives? Containment info? Causes? Evidence?

[onlylonely]

Hi Paul,

Yup, when I removed me.dirty = false it works fine and the unique number show in the column, but the number won’t increment.

If add me.dirty= false. Column won’t show the unique number. I’m also thinking why sequence number keep on remain 0.

My point was that while you added the line to set the variable, you took out

Me.Dirty = False

so the record is not going to save automatically. In a brief test the code worked when I added that back. I did have to fix the first record, which had a sequence of 0.

[onlylonely]

Hi orange,

All will be supported by the database. All those information will store into the table. But I will split it into 3 tables.

Based on your comments, attached is a rough overview of the process(es) involved.

How much of this will be supported by your database? Violations, Objectives? Containment info? Causes? Evidence?

[pbaldy]

Oh, and the textbox with sequence has to be bound. That's why the sequence field is always 0.

[onlylonely]

Any idea to let the sequence + 1 for bound form?

Oh, and the textbox with sequence has to be bound. That's why the sequence field is always 0.

[pbaldy]

I guess I wasn't clear. The sequence textbox is not bound (has no control source). It needs to be bound to the sequence field:

[onlylonely]

Hi Paul,

Stupid me, i overlooked this and it works fine after i bound it.
But another problem come , my unique number is ANC2005 + 2 digits sequence. But now i'm getting 1 digit only. ANC20051 instead of ANC200501.
Any idea?

I guess I wasn't clear. The sequence textbox is not bound (has no control source). It needs to be bound to the sequence field:

[pbaldy]

I'm not at my computer. Are you still using the Format() function on the sequence field? That should get you two digits.

[onlylonely]

Hi Paul,

Yes. When form load . i'm using format for sequence.

Private Sub Form_Load()

Dim LValue As String
Dim A As Date


A = Now()


MsgBox (A)


LValue = Format(A, "YYMM")


Forms!F1NCform.txtsequence = Format(Nz(DMax("Sequence", "Query3", "YYMM = '" & LValue & "'"), 0) + 1, "00")


Forms!F1NCform.txtscar = "ANC" & LValue & Me.txtsequence


'Me.Dirty = False


End Sub

I'm not at my computer. Are you still using the Format() function on the sequence field? That should get you two digits.

[onlylonely]

I got it... i just need to change the number to short text in F1 table then will do.

[accesstos]

Hi onlylonely!

Take a look in attachment for an unbound form solution (form "F1NCForm").

I have made some major modifications in "F1NCtbl" and "F1NCForm":

1. The "fill" controls in "F1NCForm" have the names of the corresponding fields in table "F1NCtbl" for code reduction and flexibility.

2. The PK of "F1NCtbl" now is the [CAR_ID] (Autonumber), not the SCAR.

3. Fields [SCAR] and [YYMM] have removed from table "F1NCtbl" and the query "qryF1NC" provides the [SCAR] code plus the [CAR_Y] (the year of [CAR_Date]) and [CAR_M] (the month of [CAR_Date]) instead of [YYMM] for more secured searches.

For example:

"WHERE [CAR_Y]=" & Year(Date) & " AND [CAR_M]=" Month(Date)

instead of

"WHERE [YYMM]='" Format(Date;"YYMM") & "'"

Also, you will find many changes in code of "F1NCForm".

I hope it helps!
John

[onlylonely]

Hi John,

I've solved that issue. Now my issue is bound form cannot bound back to query.

Appreciate if some1 can help me out.

1) Click listbox
2) double click one of the list
3) then click back

Not sure why it will not bound to F1.

Hi onlylonely!

Take a look in attachment for an unbound form solution (form "F1NCForm").

I have made some major modifications in "F1NCtbl" and "F1NCForm":

1. The "fill" controls in "F1NCForm" have the names of the corresponding fields in table "F1NCtbl" for code reduction and flexibility.

2. The PK of "F1NCtbl" now is the [CAR_ID] (Autonumber), not the SCAR.

3. Fields [SCAR] and [YYMM] have removed from table "F1NCtbl" and the query "qryF1NC" provides the [SCAR] code plus the [CAR_Y] (the year of [CAR_Date]) and [CAR_M] (the month of [CAR_Date]) instead of [YYMM] for more secured searches.

For example:

"WHERE [CAR_Y]=" & Year(Date) & " AND [CAR_M]=" Month(Date)

instead of

"WHERE [YYMM]='" Format(Date;"YYMM") & "'"

Also, you will find many changes in code of "F1NCForm".

I hope it helps!
John

[pbaldy]

I'm not clear on what you're describing, but this line:

DoCmd.OpenForm "F1NCForm", , , "SCAR = '" & Forms!F2CAPAform!txtscar & "'"

Is not going to work as expected, opening the form to that record, because the form's Data Entry property is yes. You can override that by adding an argument at the end:

DoCmd.OpenForm "F1NCForm", , , "SCAR = '" & Forms!F2CAPAform!txtscar & "'", acFormEdit

[onlylonely]

Hi Paul,

Millions thanks for your advice. Instead of putting acformedit. I've put acformread, as i only need to view back and not edit.
But another problem comes up. When i put acformread, i'm not able to click the link and direct me to the file. Any idea?

I'm not clear on what you're describing, but this line:

DoCmd.OpenForm "F1NCForm", , , "SCAR = '" & Forms!F2CAPAform!txtscar & "'"

Is not going to work as expected, opening the form to that record, because the form's Data Entry property is yes. You can override that by adding an argument at the end:

DoCmd.OpenForm "F1NCForm", , , "SCAR = '" & Forms!F2CAPAform!txtscar & "'", acFormEdit