populating textfields from tables

[newbie87]

Hi,

I have a form called RequestInformation where users can insert new details and modify their existing details from data that is stored in my tables.

if a user inserts new data this becomes Version 1 and if a user amends minor changes to their details this becomes Version 2 and so on.

The users details are stored in the database so they can see all of their changes and previous details.

To see previous and current details on the RequestInformation form there is a combo box where the user can select Version 1, Version 2 and Version 3. If the user choose's version 1, they can view their first inputted details, if they choose Version 2 they can see the modified data, and Version 3 shows their latest amendments. Once the version is chosen by the user I want it to populate the correct textfields on the RequestInformation form, for example, age would populate the age textfield and forename would populate the forename textfield.

I'm having trouble with selecting a chosen Version, As this will be a large amount of code, i will just post the code for Version 1 as the rest will be the same, this is my code so far

If (cmbVersion = "Version 1") Or cmbVersion = "Version 1" Then
CurrentDb.Execute "SELECT Personal_Details.ID, Personal_Details.Forename, Personal_Details.Surname, Personal_Details.Age, Personal_Details.Gender, " _
& "DeliveryDetails.Address1, DeliveryDetails.Address2, DeliveryDetails.PostCode, " _
& "FROM (Personal_Details INNER JOIN DeliveryDetails ON Personal_Details.ID = DeliverryDetails.ID) " _
& "WHERE(((Personal_Details.ID = DeliveryDetails.ID))) And (((Personal_Details.Version) = 1))"
end if

any help would be appreciated

 

[pbaldy]

I think you're making it more complicated than it needs to be. You can simply base your form/subform on a parameter query that gets the version number from your combo. Then all you have to do is requery it.

The problem with your code is that you can't "Execute" a simple SELECT query. You'd probably want to set the record source of your form to that SQL.

 

[Annoscia85]

Can you not use DLookups?

so for example

If (cmbVersion = "Version 1") Then
Me.Forename = DLookup ("Forename", "Personal_Details", "Version = 1 AND Forename = '" & Forename & "')
end if

this populates the Forename textfield (Me.Forename) where the user selects version 1?

 

[newbie87]

thanks annoscia85 this worked, and i was able to do as i needed. its easier than i thought it would be