Query - I need a field to equal the unbound control on the form... 1

[danneedham]

Hi All

I hope someone can help me!

I have a table, which i need two field to be automatically filled in when a new record is added.

I thought i could do this through a query. I need two fields to equal the values in an unbound form.

I have so far for one of the fields:
Week: Forms!Sales_Week.Week.Value

This does not return any values? I have also tried replacing .Value with .Text.

Thanks

Dan

 

[fneily]

Two mixed questions there.
If you're referencing a field on an Open form (note the word open) for a query, the syntax is Forms![Formname]![ControlName]

On an unbound control, on the AfterUpdate event of it, you can put:
Private Sub ControlName_AfterUpdate()
Dim db As Dao.Database
Dim rs As Dao.Recordset
Dim strWhere As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("TableName", dbOpenDynaset)
strWhere = "[PrimaryKey] = " & Me![ControlNamePrimaryKey]
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "No match found"
Else
rs.Edit
rs![FieldnameInTable] = Me![ControlOnForm]
etc.
rs.Update
End If
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

Also, can't you first bring in those fields with the others through a query?

 

[danneedham]

Hey

Sorry i dont think i was very clear...

Currently, the user will type in the year and then the week number in two unbound controlls. I need to then open up a table where two fields will be referenced to these controlls, however multiple records may be created.

Does this make sense? When referencing to the control on the form, it just gives blank characters??

Thanks

Dan

 

[SoCalAccessPro]

Forms!Sales_Week.Week.Value

Ditch the .value at the end of your reference. To explicitly reference a controls value, you first have to set focus to the control.

Normally, I jet reference the control, as value or .text are the default valuesw Access will return.

Ex: YourVar = Forms!Sales_Week.Week

Also, I don't think Week is a reserved keyword, but I';d still stay away from naming a control week.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[danneedham]

Hi

This is what im trying at the moment...

SELECT RTS_Estimate.Store, RTS_Estimate.Sun, RTS_Estimate.Mon, RTS_Estimate.Tue, RTS_Estimate.Wed, RTS_Estimate.Thur, RTS_Estimate.Fri, RTS_Estimate.Sat, [Forms]![Sales_week]![Week] AS Expr1
FROM RTS_Estimate;

Still no luck... Have i missed something??

 

[fneily]

Currently, the user will type in the year and then the week number in two unbound controlls. I need to then open up a table where two fields will be referenced to these controlls," So you will use these textboxes as CRITERIA, not as new fields in a query.
It would help if you posted your table structure. Does it have fields containing year and week? We don't know - can't see your table.

Also, if you actually have fields mon, tues, wed, etc. then your table is not normalized. See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

What you have is Day, Day, Day, etc. repeating column headings. Not normalized. Your table should look like:
tblEstimate
EstimateID Store EstDate Cost Etc.
Est1 5 07/20/2008 $10 etc.
Est2 5 08/01/2008 $20 etc.