populating form from recordsource (1 Viewer)

[JJSHEP89]

I am trying to give the users of my database a method to edit an existing record in the database. there is a projects list that is shown on a main form in a listbox, from this list box i want to be able to double click a line and have it open the pop up form that is used to enter the data, but instead of entering the data on a blank form, have the form already populated with the data from the selected listbox entry. Here is what i currently have saved to the double click event for the listbox...

Private Sub lstProjectLog_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset
Dim lngRow As Long

      lngRow = -1
   Do
        lngRow = lngRow + 1
   Loop Until (Me.lstProjectLog.Selected(lngRow) = True)
   
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Projects WHERE P_ID = '" & Me.lstProjectLog.Column(0, "lngRow") & "'", dbOpenDynaset, dbSeeChanges)

    DoCmd.OpenForm "Entry_Project", acNormal, , "P_ID = " & rs!P_ID, , acWindowNormal

End Sub

When i run this i get a "Run-Time error '3464': Data type mismatch in criteria expression" and my 'Set rs =.....' line is highlighted. what am i doing wrong here?

 

[Ranman256]

Column only has 1 argument

Me.lstProjectLog.Column(0)

 

[pbaldy]

Column can have the second argument, but I'd expect it to be numeric, so drop the quotes around your variable. Also, if P_ID has a numeric data type, you don't want the single quotes around the value.

 

[Cronk]

In any case, if the list box is only being used for selecting a particular record there is no use to use the Selected property, nor loop through all rows. Just use

......WHERE P_ID = " & Me.lstProjectLog

 

[JJSHEP89]

Column can have the second argument, but I'd expect it to be numeric, so drop the quotes around your variable. Also, if P_ID has a numeric data type, you don't want the single quotes around the value.

ah, removing the quotes worked, now my form pops up just fine but its completely empty.

 

[pbaldy]

What is the data type of that field? You're treating it differently in different lines of code. If it's numeric, no single quotes around the value.

 

[JJSHEP89]

What is the data type of that field? You're treating it differently in different lines of code. If it's numeric, no single quotes around the value.

its numeric, Long Interger to be specific. its just an autonumber type field used as the primary key

 

[pbaldy]

So, did you try taking the quotes out of the Set line, which treats it as text? If it still doesn't work, add a breakpoint or use Debug.Print to see what rs!P_ID is.

 

[JJSHEP89]

So, did you try taking the quotes out of the Set line, which treats it as text? If it still doesn't work, add a breakpoint or use Debug.Print to see what rs!P_ID is.

yes, i removed the quotes and the code executed just fine, but now the form opens up empty. The rs!P_ID returns the right value and it shows up in my filter property for the form as it should... not sure why the textboxes arent populating though.

 

[pbaldy]

Is the Data Entry property of the form set to Yes? Can you attach the db here?

 

[Cronk]

Actually why bother opening the recordset to find the P_ID value when you already have it in the list box property. Just use
DoCmd.OpenForm "Entry_Project", acNormal, , "P_ID = " & Me.lstProjectLog, , acWindowNormal

Paul is asking for a copy of the database. I'd just ask if P_ID is included in the data source for the form and whether the particular value you are using to test is included in the recordsource.

 

[pbaldy]

Good point. I was overly focused on fixing what was broken, didn't fully analyze it.

 

[Cronk]

And how many times have I done exactly the same thing. Woods and trees, eh?

 

[pbaldy]

Advancing age has its perks...and drawbacks.

 

[JJSHEP89]

man today has been a day of putting out fires... (figuratively) now back to this database thing....

Ok so due to size limitations and bureaucratic red tape, I've attached a stripped down database here with some sample data. there are a lot of links to tables, query's, etc. that are broken/missing because they were removed for the stripped version but they aren't relevant to this issue anyways.

When you first open the database, open the form qryDieBook (yes i know its named weird, trust me im going to change it) Once the form is open type "aa100" into the search bar in the upper left and then go to the projects tab.

View attachment Info Center-str.accdb

 

[pbaldy]

Data Entry on Entry_Project is set to Yes, as I asked about earlier. That prevents existing records from showing. Change that to No and use the DataMode argument of OpenForm to control how it opens.

It also needs P_ID added to its source.

 

[JJSHEP89]

Data Entry on Entry_Project is set to Yes, as I asked about earlier. That prevents existing records from showing. Change that to No and use the DataMode argument of OpenForm to control how it opens.

It also needs P_ID added to its source.

that worked beautifully thank you! I haven't learned about all the different form properties and what they do yet so at least now i've got one less.

 

[pbaldy]

Happy to help!

 

[JJSHEP89]

so i have a similar situation here and using the same methodology as described earlier in this post i get a different result.

Private Sub lstRecentEntries_DblClick(Cancel As Integer)
    If InStr((InStr(strRESQL, "FROM")), strRESQL, "PressCallLog") = 0 Then
        DoCmd.OpenForm "Entry_Maintenance", acNormal, , "DML_ID =" & Me.lstRecentEntries, , acDialog
    Else
        DoCmd.OpenForm "Entry_PressCall", acNormal, , "PCL_ID =" & Me.lstRecentEntries, , acDialog
    End If
End Sub

for some reason the Me.lstRecentEntries (which is the name of my listbox) returns a null value when a selection is made. why is it this worked before but not now? the data type is still an autonumber, everything works great up untill the DoCmd.OpenForm

 

[pbaldy]

I don't see that in the db you posted. Is it multi-select? You need code to get selections from a multi-select listbox.