Button to auto-fill a field in all records

[GW McLintock]

I am somewhat experienced in Access -- I know all of the main features and can even do a bit with macros and VBA, but I'm by no means an expert. I need help creating a button to auto-fill a field in all the records in a table.

I have a database centered around a table called tblCand; each record contains a date field called intDate. On the main form, initDash, when the user hits a certain button, a window pops up (either a message box or perhaps a separate form) that asks the user to type in a date, which would be stored as intDateDB. When the user hits OK, I want the value of intDateDB to replace the value of intDate in all the records in tblCand.

I'm not sure whether this is called auto-filling or auto-populating, but hopefully someone understands what I'm trying to do and can help me out.

Thank you!

-J.

 

[pbaldy]

An update query without a criteria would update all records. It could get the date from a form.

 

[GW McLintock]

An update query without a criteria would update all records. It could get the date from a form.

Took me a bit to figure out what an update query is... but it works perfectly -- thank you so much!

In case others are interested, I had the button open a form which saved intDateDB into a table of its own, and used an update query to update intDate to intDateDB.

Is there a way that I can use this query to auto-populate the intDate field in any new records? Right now I have a button that opens a form to a new record using a macro -- can I fit it in there somewhere? Or should I just use the update query after all records have been created?

Thanks again,

-J.

 

[pbaldy]

I haven't tried, but you can try using DLookup() in the default value property of the textbox on the form used to add records. If that doesn't work, I assume you could add it to your macro (I don't use macros, only VBA; it could certainly be done in VBA).

 

[GW McLintock]

(... it could certainly be done in VBA).

How could it be done with VBA, if you don't mind me asking? I could certainly use that instead. I think it would just be a matter of defining the value but I'm not so sure how to go about that.

Here's what it looks like now:

Private Sub cmdNewCand_initDash_Click()
On Error GoTo cmdNewCand_initDash_Click_Err
    DoCmd.OpenForm "frmCandInfo", acNormal, "", "", , acNormal
    DoCmd.GoToRecord , "", acNewRec
 
cmdNewCand_initDash_Click_Exit:
    Exit Sub
 
cmdNewCand_initDash_Click_Err:
    MsgBox Error$
    Resume cmdNewCand_initDash_Click_Exit
 
End Sub

I think I would insert the code right in that first part, just after the GoToRecord command. Am I on the right track? (Note that I taught myself basic VBA in the last few weeks .)

-J.

 

[pbaldy]

That is VBA. After the GoToRecord line, this type of thing:

Forms!frmCandInfo.DateTextboxName = DLookup(...)

filling in the field and table arguments of the DLookup(). Presuming that's a one-record table, no criteria is needed.

 

[GW McLintock]

That is VBA. After the GoToRecord line, this type of thing:

Forms!frmCandInfo.DateTextboxName = DLookup(...)

filling in the field and table arguments of the DLookup(). Presuming that's a one-record table, no criteria is needed.

Could you help me with the DLookup()? I'm just really not that familiar with it and am learning as I go along. I got this far:

Forms!frmCandInfo.intDate = DLookup(...)

But the arguments for DLookup are confusing me. The table tblIntDate like you said is a single-record table, but I'm confused about the syntax required here. I know I am trying to refer to intDateDB from tblIntDate, but how do I write it out?

-J.

 

[pbaldy]

DLookup("FieldName", "TableName")

 

[GW McLintock]

DLookup("FieldName", "TableName")

Wow, that was simpler than I thought One (hopefully final!) concern -- I did that and it seems to work, but when I open the form I see the (correct) date flash and then it disappears leaving the field blank. Any idea as to what could be causing that? Maybe a property gone rogue?

-J.

 

[pbaldy]

Not offhand. Perhaps there's something else running in the form itself? Can you post the db here?

 

[GW McLintock]

Not offhand. Perhaps there's something else running in the form itself? Can you post the db here?

I'd have to take out some stuff but maybe in a bit. But I think I figured out what was wrong -- I put the code on the wrong button :banghead: All fixed now and it works perfectly!!!

Thanks so much for your help!

-J.

 

[pbaldy]

Happy to help!