By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,050 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,050 IT Pros & Developers. It's quick & easy.

MS Access '97 - How do i return a record of data from a list box presented in a form

P: 33
Hi,
New user here and using a ms access 97 database.
I have a form containing a list box which presents the contents of a table.

On double clicking a record, i need the record to be presented in a new form where further data can be added.

I set the event procedure against the list box on Double Click, i can then use the DoCmd.OpenForm "frmDataEntry" but how do i pass the value that ive selected (double clicked), to then populate the form?

Many tks
Jan 30 '09 #1
Share this Question
Share on Google+
18 Replies


DonRayner
Expert 100+
P: 489
the DoCmd systax is as follows

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)
You would be looking to set the WhereCondition which would make your docmd look something like this

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmDataEntry",,,"[YourControl] = " & "'" &[ListBox] & "'""
"YourControl" would be the name of the matching control on frmDataEntry
"ListBox" would be the name of the listbox you are double clicking on.
Jan 30 '09 #2

P: 33
Don,
tks for the quick reply.Im now receiving a syntax error on the following code;

DoCmd.OpenForm "frmDataEntry", , , "[frmDataEntry.txtRepairRef] = " & "'" & [lstEnquiry] & "'"""

To try and resolve I've tried it without the the form name eg;
DoCmd.OpenForm "frmDataEntry", , , "[txtRepairRef] = " & "'" & [lstEnquiry] & "'"""

I also tried replacing the form name and field with the table name and field.

Im going wrong somewhere
Appreciate any advice.
Jan 30 '09 #3

DonRayner
Expert 100+
P: 489
@Quizzed
It should be like this.

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmDataEntry", , , "[txtRepairRef] = " & "'" & [lstEnquiry] & "'""
And it looks like you have an extra " at the end

First set of quotes is " ' "
Second set of quotes is " ' " "

Let me know how it works.
Jan 30 '09 #4

P: 33
Still receive the syntax error.
When i move away from the code, there is an additional " applied as the last character - Is this whats causing the syntax error and if so is there any way of stopping this?
Jan 30 '09 #5

DonRayner
Expert 100+
P: 489
Ok Try this instead

Expand|Select|Wrap|Line Numbers
  1. Dim strCondition as string
  2. strCondition = "[txtRepairRef] = " & """" & me.lstEnquiry & """"
  3. DoCmd.OpenForm "frmDataEntry", , , strCondition
  4.  
]
Jan 30 '09 #6

P: 33
It now prompts me to enter the value for the field txtRepairRef

If i then enter a value which i know exists, it does then open the form but without any of the associated data from the underlying table.
Jan 30 '09 #7

DonRayner
Expert 100+
P: 489
Put the following line before the DoCmd line. and let me know what shows up in the message box.

Expand|Select|Wrap|Line Numbers
  1. msgbox strCondition
Jan 30 '09 #8

P: 33
I selected repair ref 999998 and when i dclick this now returns;
[lstEnquiry].[txtRepairRef]='999998' With an OK cmd button.

If i then select OK, it returns lstEnquiry.txtRepairRef and needs me to input a value.
Jan 30 '09 #9

DonRayner
Expert 100+
P: 489
Your mesbox should be showing

Expand|Select|Wrap|Line Numbers
  1. [txtRepairRef]='999998'
Please post back the exact code that you are using.
Jan 30 '09 #10

P: 33
I still had the ref to lstEnquiry in the code. The msgbox now does as you expected eg
[txtRepairRef]='999998'

The code is;
Private Sub lstEnquiry_DblClick(Cancel As Integer)
Dim strCondition As String

strCondition = "[txtRepairRef] = " & """" & Me.lstEnquiry & """"
MsgBox strCondition
DoCmd.OpenForm "frmDataEntry", , , strCondition
DoCmd.OpenForm "frmDataEntry", , , strCondition
Jan 30 '09 #11

DonRayner
Expert 100+
P: 489
Ok, you can get rid of the msgbox line now, You only need the docmd line once. What kind of error message are you getting now?
Jan 30 '09 #12

P: 33
Don, ive removed the msg box line and the second docmd but it still prompts me to enter the value for txtRepairRef.

Is it because the field "txtRepairRef" is on "frmDataEntry" which is unbound from table tblDataTrack?

The operating logic of the db is that from a main menu they will open the frmDataEntry and complete approx 10 fields of a approx 20 fields,and save this to table tblDataTrack. I have then introduced a from containing an option group, depending on which option changes the results in the List change.

Basically i need to provide a solution that will allow them to search for a record that is stored in tblDataTrack and present this within the form frmDataEntry.

Tks for your help, appreciated.
Jan 30 '09 #13

DonRayner
Expert 100+
P: 489
@Quizzed
If the field txtRepairRef is unbound as you say then you can't use. You need to refer to a control that is bound to the field in your table tblDataTrack that has the appropiate data. The bound control does not have to be visible so if needed you can just add another control with it's visible property set to false.
Jan 30 '09 #14

P: 33
Ok, ive added the primary key field of [ID] to the form and it has a control source of ID - this is the autonumber primary key on the table.
The code now looks like this;
strCondition = "[ID] = " & """" & Me.lstEnquiry & """"
DoCmd.OpenForm "frmDataEntry", , , strCondition

It still then asks me to enter the ID again
Jan 30 '09 #15

Expert 100+
P: 1,287
ID being a number, you don't need quotes around the value as you would for a string.
Expand|Select|Wrap|Line Numbers
  1. strCondition = ""
  2. if not isnull(Me.lstEnquiry) then
  3.     strCondition = "[ID] = " & Me.lstEnquiry
  4. end if
  5. DoCmd.OpenForm "frmDataEntry", , , strCondition
Feb 2 '09 #16

DonRayner
Expert 100+
P: 489
@Quizzed
I don't know where my head was at last week..... But I've lead you astray.
The strCondition should look like this
Expand|Select|Wrap|Line Numbers
  1. strcondition = "((TableName.FieldName=" & Me.lstEnquiry & "))"
TableName = the name of the forms underlaying table or query
FieldName = the Field in the table/query that contains the data you want to filter.
Feb 3 '09 #17

P: 33
Tks Don and ChipR, thats sorted it. Appreciate your help, cheers.
Feb 3 '09 #18

DonRayner
Expert 100+
P: 489
You're quite welcome
Feb 3 '09 #19

Post your reply

Sign in to post your reply or Sign up for a free account.