Fields in a form with vba code (1 Viewer)

ilanray

Member
Local time
Today, 06:41
Joined
Jan 3, 2023
Messages
118
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
Code:
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

Well-known member
Local time
Yesterday, 23:41
Joined
Apr 9, 2015
Messages
4,337
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

Super Moderator
Staff member
Local time
Today, 04:41
Joined
Feb 19, 2013
Messages
16,731
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
 

ilanray

Member
Local time
Today, 06:41
Joined
Jan 3, 2023
Messages
118
I mention 2 records because the first is the ID and i don't need to display it
 

ilanray

Member
Local time
Today, 06:41
Joined
Jan 3, 2023
Messages
118
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

Super Moderator
Staff member
Local time
Today, 04:41
Joined
Feb 19, 2013
Messages
16,731
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

Member
Local time
Today, 06:41
Joined
Jan 3, 2023
Messages
118
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

Super Moderator
Staff member
Local time
Today, 04:41
Joined
Feb 19, 2013
Messages
16,731
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 22:41
Joined
Feb 28, 2001
Messages
27,488
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:

Code:
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).
 

Users who are viewing this thread

Top Bottom