Once only data entry for multiple rows

[Immatoity]

Hi

I have a question...bear in mind I am very "raw" in terms of Access..
I have a database that , to cut a long story short, throws out a big journal in the form of a table (which is produced as a result of a query)

One of the fields of the table is "Narrative" and it contains data along the lines of "M368OJB Acc Damage Wk" - obviously this is repeated for as many rows as the table has (this varies on a weekly basis).

All I want to do is get the week number in the narrative for all lines, but I only want the user to have to enter this week number once.

I would end up with narrative "M368OJB Acc Damage Wk44" or similar...

Whats the easiest way to do this, ensuring the user can only enter the week number once...

ta

[xCav8r]

Sounds like you just need a query that returns all records where the narrative field has the week number that a user inputs. You can accomplish that in a variety of different ways. How were you thinking of getting the user to input that week number? From a form? And what do you need to do with the results of the query once it?s run? Run a report?

[Immatoity]

Hi.. I was thinking of a form, but bear in mind I have never produced one...

I just want the week number to be added to a table that I then output to excel...the table is produced by a query...

Could the form essentially "tag on" the week number to each narrative with the string "Wk" ??

:help

[xCav8r]

OIC where you're coming from. I misunderstood what you wanted to do. You want to append Wk## to whatever text is in the narrative field. That's easy to do with an update query. Have you tried using that?

I made you an example. Take a look at the attached. Open frmSelectWeekNumber, input your number, and hit the button. That'll run this code:

Option Compare Database
Option Explicit



Private Sub cmdUpdateRecords_Click()

Dim strSQL As String

' Example to demonstrate updating all records

strSQL = "UPDATE tblBefore SET tblBefore.Narrative = tblBefore.Narrative & ' ' &" _
& " 'WK" & Me.txtWeekNumber.Value & "';"

' Example to demonstrate selectively updating records

' strSQL = "UPDATE tblBefore SET tblBefore.Narrative = tblBefore.Narrative & ' ' &" _
' & " 'WK" & Me.txtWeekNumber.Value & "' WHERE tblBefore.Narrative > '5';"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.sfrmBefore.Requery

End Sub

Private Sub Form_Load()

' This puts the current weeknumber into the textbox
Me.txtWeekNumber.Value = Format(Now, "ww", vbUseSystemDayOfWeek, vbUseSystem)

End Sub