Fields in a form with vba code (1 Viewer)

[ilanray]

Hi
I have a table with 3 fields (id,Task_id,description) and the values looks like
id task_id description
--------------------------
1 1444 aaaaaa
2 1444 bbbbb
3 1555 cccc
I would like to create a form with 3 unbound textbox and fill the textbox something like

dim rs as recordset
dim db as database
set db=CurrentDb
set rs=db.OpenRecordset("select * from TaskSub where task_id=1444")
do while not rs.EOF
    me.textbox1 - rs("description")
    me.textbox2 = rs("Task_id")
        rs.MoveNext
loop

The problem with that is it create 2 lines at the form but with the same last value
I would like too 2 line with aaaa and bbbb

Someine Can help me please?

 

[Ranman256]

with a single click , access will make a form and put the fields in it all connected to the data.
why do you want to make more work by putting code in it?

(besides, you cant load multi record forms using vb that way.)

 

[CJ_London]

why unbound? what is wrong with binding them?

Can you clarify what you actually have? You say you have 3 unbound textboxes but only populate 2? Is the form continuous or single? Does the form have a recordsource and other bound controls?

what does 'I would like too 2 line with aaaa and bbbb' actually mean? Suggest show us

Otherwise suggest investigate using a crosstab query or possibly the concatrelated function

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[ilanray]

I mention 2 records because the first is the ID and i don't need to display it

 

[ilanray]

with a single click , access will make a form and put the fields in it all connected to the data.
why do you want to make more work by putting code in it?

(besides, you cant load multi record forms using vb that way.)

I have to use unbound because i have a query with and one of the fields in checkbox' when I an using a query I can't mark the checkbox

 

[CJ_London]

unless you provide more information as requested, don't think you will get more help. And you can mix bound and unbound controls on a form

 

[ilanray]

I have 2 table one is for tasks with fields (id,task_id,userid,checked) and employee with fields (userid, fullname)
the join is task.userid = employee.userid
If I am creating a form with bound fields from the query and I would like to click on the checked field to change it , it wouldn't allow me since it is a query

 

[CJ_London]

just repeating what you have already said is no help. I don't have the time to spending guessing and suggesting many different ways, one of which might suit your needs.

Take a look at this link - perhaps that does what you want

A multi select form using unbound check boxes

Thought I would put this up since it is a fairly common topic and I have put it up elsewhere - selecting records from a list for onward processing without the need for a table based tickbox. Similar in concept to the conditional formatting version I provided here...

www.access-programmers.co.uk

 

[MajP]

I demo several techniques but found that @CJ_London technique to require the least work for the performance
Post in thread 'Manipulating an independent check box field in a form' https://www.access-programmers.co.u...check-box-field-in-a-form.324720/post-1843895

 

[The_Doc_Man]

If I am creating a form with bound fields from the query and I would like to click on the checked field to change it , it wouldn't allow me since it is a query

This statement is categorically not true. You will need to look at the form itself to determine why you cannot make that change. I have used fields bound to queries - with JOINs - many times. They update quite well.

You have an ID field (hopefully the prime key?) not participating in the form, and that is actually significant. Even if you don't show that ID field, the fact that it exists means that your form COULD allow the update. But at the moment based on your description, the form cannot do the update due to ambiguity. I will explain.

Your source-table data looks like this:

1  1444  aaaaaa
2  1444  bbbbb
3  1555  cccc

IF you consider ONLY the 2nd and 3rd fields AND consider that field 2 is NOT marked as unique (nor is field 3), that JOIN to your employee table potentially has a choice regarding where to store data - and it doesn't matter if you were on the 3rd record at the time. Access is looking at the field properties, not the contents, when it makes that determination. Looking at the non-unique fields, the POTENTIAL ambiguity is there.

A form is designed to store a single record (the current record). For a form, it must be unambiguous as to WHERE to store it. And without the ID field, the destination is not uniquely specified. If the ID field is NOT unique, then you will still have the problem even with it present.

I think the solution for this is to bring the ID field into the form even if you only display it in a locked field (.Locked = TRUE... cannot be updated through the form) or an invisible field (.Visible=FALSE).

 

[ilanray]

Thanks