Listbox Value To Textbox To Table

[lhooker]

I have a form ('Form1') that has a listbox ('Listbox1'). I want to double click on the listbox value and select a date from a textbox ('Date/Time' type) and add this record to an existing table. I figured out to get the value from a listbox (see below), but can't figure out how to add the listbox value and date to a row in a table. Can someone give me an example ?

=[List1].[Column](0)

 

[pbaldy]

If the listbox in single select, the simplest solution is to bind the form to the table and the 2 controls to their respective fields.

 

[isladogs]

Or perhaps something like this on a button click event depending on whether you need a new record (APPEND) or edit an existing record (UPDATE)

Replace items in bold with names of your table & fields

Dim strText As String, dteDate As Date

strText = Me.List1.Column(1)
dteDate = Format(Me.txtDate,"mm/dd/yyyy")

'create a new record
CurrentDB.Execute "INSERT INTO [B]MyTableName ( TextFieldName, DateFieldName[/B] )
SELECT '" & strText & "' AS [B]TextFieldName[/B], #" & dteDate & "# AS [B]DateFieldName[/B]", dbFailOnError

'OR update an existing record
CurrentDB.Execute "UPDATE [B]MyTableName[/B] SET [B]TextFieldName[/B] = '" & strText & "', [B]DateFieldName[/B]= #" & dteDate & "#",dbFailOnError

BTW - you asked about Julian dates yesterday in another thread but haven't answered questions set by other users about what you meant.
I interpreted it one way, others another way. Please clarify so you get a definitive answer

 

[lhooker]

Ridders,

Sorry for the delay. I have not had time to devote for the project that I'm working on. To answer your question, I'm was referring to ===> yyddd date format. Thanks ! ! !

 

[arnelgp]

if the field you want to save is of Date type use this function
to convert your Julian date to Real date:

Public Function JulianToDate(dt As Variant) As Date
    dt = dt & ""
    JulianToDate = DateSerial(Left(Year(Date), 2) & Left(dt, 2), 1, Right(dt, 3))
End Function

to insert it in your table:

DoCmd.RunSQL "Insert Into yourTable (dateFieldName) SELECT JulianToDate(" & Me.txtJulian & ")"

 

[lhooker]

Arnelgp,

Thanks ! ! ! I'll try this . . .